Costing and inventory closing

What’s this all about ?

From my personal experience of discussions with consultants and developers, implementing Dynamics AX, issues related to costing and an inventory closing became one of  main pain-points of an implementation process. Peoples tends to treat an inventory closing as a kind of black box, which, depending from phase of the moon, can produces different result and or require different time to run for a same dataset. To my opinion, it is caused by costing information being scattered across various parts of the documentation. In addition, some peculiarities of costing are not described at all and should be learned in a hard way with trial and error approach or by learning of X++ code of an inventory costing.

In this article I am trying to provide reasonably detailed description of an inventory closing and also I am discussing some other costing related issues.

This document is intended for more or less experienced consultants, who already knew a general architecture of the Trade module and also have some (successful or unsuccessful) experience in usage of an inventory closing.
Almost two years has passed since original version of the article was published. Axapta made considerable progress during these two years (DAX 2009 was released, which introduced some radical improvements in costing area). Besides, I want to describe some subtle details which have been omited from the first version of the article. All additions to the original article are marked out in italics.

Why does an inventory closing is necessary ?

The main task of an inventory closing is to calculate the actual cost price of an inventory issue. DAX works with  inventory costs in the following way: On instant of an inventory issue posting, system does not tries to calculate an exact issue cost price according with the inventory model. Instead of this, system uses some estimated value for the issue cost price, which looks like a good approximation for the actual cost price. (To be discussed  in the Instant estimated cost price section). Then, during the inventory closing, the actual issue cost price is calculated with respect to inventory model settings. After that, system calculates the difference between the initial (estimated) issue cost price and the true issue cost price, then system writes this down into inventory transactions and inventory adjustments. Then – GL postings for these adjustments are created. Why this approach has been chosen? In a general, during a posting of an initial inventory issue, system might not have all information required to calculate a true cost price. For example, if negative inventory is turned on, we can post issues before corresponding receipts; In the Weighted Average model, it is impossible to calculate an issue cost price before all the receipts for a period has been posted; For FIFO/LIFO models, the receipts might be posted out of chronological order and so on.

When I was working as a DAX developer, from time to time newbie consultants were trying to talk me into customizing Axapta to allow an Accountant to manually enter an issue cost price into inventory documents. Then  Axapta was proposed not to touch inventory transactions having manual cost price during an inventory closing. Although this approach looks technically viable from the developer’s point of view, it STRONGLY conflicts with the very idea of the Axapta’s costing system. A true issue cost price SHALL always be calculated during an inventory closing. An on-the-fly cost calculation or even more – a manual costing are impossible on principle.

After actual issue cost prices for a period has been calculated, system disables creation of the new financial inventory transactions in this period. It looks quite reasonable, because a new transaction creation could break the order of issue to receipt settlements thus resulting in incorrect issue costs. If we only want to calculate preliminary issue costs for the current inventory transactions snapshot, we would rather use an inventory recalculation procedure instead of the complete inventory closing

So – Inventory closing procedure consists from the following steps:

  1. Issue to receipt settlement. Calculation of a initial cost of issues.
  2. Cost propagation via inventory movements graph
  3. Posting of adjustments into GL

Let’s look closer on every of these steps:

Receipt-to-issue settlement

A calculation of an actual cost price in Axapta is always performed from the settlement between receipts and issues. For FIFO and LIFO principles this approach looks quite natural. Say, We purchased 2 pieces for 10EUR apiece, then 3 pieces for 14EUR a piece. To calculate the cost price for the issue of 3 pieces we must settle it to the first receipt of 2 pieces with the total value of 20EUR, then with one third of the second receipt with the total value of 14EUR,thus resulting in the issue’s cost price of 34EUR. During the settlement procedure, a receipt’s contribution to the issue cost price is calculated as a balance of the receipt transaction on the date of the settlement multiplied to the ratio between the settlement’s quantity and the total quantity from the receipt. Of course – the result is rounded according to rounding rules from the currency table.

I will not describe in details the mechanics of the FIFO model’s settlement, for it is a straightforward process. The only thing that is worth to mention is that the system does not check consistency between the issue and receipt dates. Say – we booked an issue of the item on the 5th of month and a receipt of the same item on the 15th of month (suppose – there are no other movements for the given item). In this case, system would settle the given issue to the given receipt without any warnings, which is contrary to mere common sense. I will look closer on  reasons and negative consequences of having negative inventory in sections ‘Why does negative inventory appears?‘ and ‘Cost propagation‘.

It would be more correct to call LIFO and ‘LIFO on date’ models a LILO model (Last In Last Out). It is a kind of direct opposite to the FIFO model. System runs through the list of unsettled issues beginning from the last one, and settles them to the last unsettled receipt (ordered by the date of course). A “LIFO on date” model differs from the LIFO in the aspect of finding an appropriate receipt for the given issue. This model settles an  issue to the last receipt that have been received BEFORE date of the issue. Say, we purchased some item on the 1st, 5th and 31th days of the month and this item was sold on, say, 10th and 18th of the month. From the strict LIFO point of view, the issue from the 18th should be settled to the issue from the 31th. (Because LIFO suggest the last receipts bound to be written-off first). But the whole idea seems to be a little weird, for we are trying to issue the item long before it was actually received. So it seems that developers have implemented two similar models to satisfy both accounting and common sense approaches to the LIFO principle.

When I was studiyng a DAX2009 source code, it turned out, that actually, usage of the LILO principle, I had described earlier, had been a more programming error than an actual intention. It seems that a developer just copied the piece of code from the FIFO method and substituted the reverse keyword for both issue and receipt selection statements. That resulted in the LILO principle I’ve described above, that is – latest issues was being settled with latest receipts, then earlier issues with earlier receipts and so on. The funny thing is that this error was introduced in at least version 3.0sp2 (summer 2003) and was fixed only 4 years later – in year 2007… It seems that the LIFO principle is not very popular for inventory valuation all around the world. 🙂

Now  – Let’s have a look at ‘Weighted average’ and ‘Weighted average on date’ principles. In any old time, before Axapta 3.0sp2 was released, run of an  inventory closing procedure for these principles led to creation of an every-receipt-to-every-issue settlement. So, If we had a two unsettled receipts from the previous period, 6 receipts on the current period and, say 40 issues in the current period, this led to the settlement of 8 receipts to 40 issues, thus producing 320 settlements and 640 settlement records (For each settlement is stored as two records of inventSettlement table). It is easy to guess that such explosive growth of the invent settlement table also led to significant degradation in inventory closing’s performance. That’s why the more lite approach to  weighted average costing was implemented in version 3.0sp2. In that version, thresholds for quantity or amount of the settlement were introduced. Roughly speaking, while finding an appropriate receipts to settle with a given issue, system begins with receipts having a highest unsettled quantity and tries to settle at least the threshold value. If the unsettled quantity for issue or receipt is below the threshold value, system settles it regardless of the threshold value. The threshold value may be specified in the following ways:

  1. Minimal percent of of receipt quantity may be specified in the inventory closing parameters. In this case – the system tries to settle at least N percents of a given receipt in each settlement.
  2. In the field “Minimum Average Settle” of the inventory table for the given item.
  3. In the field “Minimum Average Settle” of the inventory parameters table. This parameters is effective only for items which do not have the specific value in the field from the previous step.

Finally – there is also an indirect way to specify a quantity to be settled. It is possible to specify a minimum amount to be settled  in inventory closing parameters. In this case, the system tries to create settlements which settle a receipt to an issue for at least specified monetary amount, thus limiting a quantity to be settled.

Thus, under regular conditions (purchase prices do not considerable vary during a reporting period; number of issues is significantly higher than number of receipts)  results of this averaging algorithm would not differs considerably from a true average cost calculated, roughly speaking, in Excel. But suppose we are purchasing 2-3 batches of item with thousands of pieces in them, and we are selling several thousands of batches with 2-3 item in each. Unexpectedly we are purchasing some strange batch with 5 pieces on double of the regular price. In this case, those unlucky issues, that were to be settled to this receipt, would have a cost price which is deviates significantly from true period’s average.

After the original article was written, a radical change to the weighted average cost calculation algorithm has been introduced.  If costing procedure finds that we have only ONE unsettled receipt, then all issues are settled to this receipt and everything is working as usual. But if there are SEVERAL unsettled (open) receipts, then a completely different algorithm is used:

  1. A dummy inventory transfer is created for a given item id and a given combination of financial inventory dimensions. That is – The inventory transfer operation appears, which is processed by inventory closing as a regular inventory transfer, but it does not actually change the inventory dimension; an inventDimId field value is the same on both issue and receipt inventory transactions of the transfer.
  2. All open receipts in a period are settled to the issue transaction of the transfer
  3. All open issues in a period are settled to the receipt transaction of the transfer.

Thus actually the receipt of this transfer has a total cost of all open receipts of the period. Then –  this cost is propagated to every actual inventory issue of the period by the ratio between  the quantity in a given issue and a total summed-up quantity from all open receipts. This algorithm is used for the ‘Weighted average’ principle. The ‘Weighted average on date’ cost is calculated with a somewhat more peculiar algorithm. Actually, the ‘Weighted average on date’ algorithm is reduced to the ‘Weighted average’ algorithm by opening a new averaging period for every receipt. That is, on an occurrence of a new receipt, the system creates the new dummy transfer and settles the new receipt (actually – several receipts sharing the same financial date – should we have them) and the issue from the previous transfer to the new dummy transfer.

I have not personally tested the performance of algorithm described. But taking some general considerations, it looks like an inventory closing for ‘Weighted average’ would work with approximately  the same performance as FIFO, and ‘Weighted average on date’ would take around 130-140% of FIFO inventory closing time.

I want to especially emphasize again that for a settlement cost’s calculation, the system uses the balance of inventory transaction on the date of the settlement. So if we purchased a merchandize on  15th, then allocated some additional markup into the receipt’s cost price on 25th, than for an inventory closing on 20th, only the initial purchase cost price, cleared from later markups would be taken into account.

Cost propagation

So – The receipt-to-issue settlement is done.  The true cost price is calculated. Why does it need some extra steps for a cost propagation ?

Imagine the following picture:

  • An item was purchased on the 1st of Jan with the cost price of 2000 EUR
  • On the 5h of Jan it was transferred to a neighboring warehouse. It is obvious that the estimated instant issue cost price as well as the receipt cost price for the transfer is 2000 EUR
  • On the 10th of Jan the item was sold from the neighboring warehouse. The estimated instant issue cost price is 2000EUR
  • On the 20th of Jan some transportation costs of 400 EUR was acquired into the initial receipt cost price (via markup allocation functionality).
  • An inventory closing model for the item is FIFO

If we are closing inventory for date of the 31th of January then system settles the issue (to the neighboring warehouse) from the 5th of Jan to the original receipt and calculates the actual cost price of the issue to be 2400 EUR. It is obvious that we should somehow change the receipt cost price for the transfer to the neighboring warehouse and, correspondingly, change the  issue cost price for the sales order from the 10 of Jan, to 2400 EUR too.

This task is implemented in the following way:

On the every update of an issue inventory transaction during an inventory closing,  the system checks whatever a linked receipt for it exists ? (Linked receipts exist for issues of inventory transfer journals, quarantine orders, transfer orders, production orders, BOM Journals and dummy transfers made for average costing). If this is a case, then the adjustment amount is written into the “Lot level adjustments” (InventCostListTrans) table, together with the lot id of the linked receipt and some additional info.

Then on a stage of cost propagation, the system reads this information, adjusts cost price for corresponding receipts, then adjusts  cost price for issues settled to this receipts. Should adjusted issues have a linked receipts, then cost propagation is repeated.

Thus, basically – a cost propagation process is iterative be the nature. Through usage of an inventory settlement table, the system builds a cost movement graph which traces cost movements from an initial purchase to a final sale.

How to define a number of iterations required for the inventory closing ? It is a complex issue. In a theory, the number of iterations required is calculated as a maximum number of inventory transfers for a given item id plus a maximum nesting level for BOMs with the given item, plus a number of transfers via quarantine order and so on. In a reality, the number of iterations required mostly depends on presence or absence of loops in a cost movement graph. If these loops are absent, then an inventory closing requires 5-7-10-15-20 iterations to be finished. If these loops are present, then the number of iterations required is scarcely predictable.

Why do loops occur in a cost movement graph ?

Imagine the following picture:

  1. On the 1st day of the month one piece of item was purchased for 200 EUR into the warehouse wh1 (Purch1)
  2. On the 20th day of the month another four pieces was purchased for 250 EUR a piece  into the warehouse wh1 (Purch2)
  3. Then a transfer of 2 pieces from wh1 to wh2 was posted (post factum) on the 5th day of the month. (Trsf1)
  4. Then this two pieces was transferred back to wh1 on the 6th day of the month. (Trsf2)
  5. All five pieces was sold on the 25th day of the month from wh1 (sale1)
  6. Then on the 27th day of the month extra costs was acquired into the cost price of the original purchase via markup allocation.
  7. A Warehouse inventory dimension is flagged as a financial inventory dimension.
  8. We about to close an inventory for the 31th day of the month.

Before an inventory is closed, the inventory transactions has the following costs and quantities:

Document Date Quantity Cost Price Warehouse
Purch1 01.01.2007 1 200 1
Trsf1 05.01.2007 -2 -480 1
Trsf1 05.01.2007 2 480 2
Trsf2 06.01.2007 -2 -480 2
Trsf2 06.01.2007 2 480 1
Purch2 20.01.2007 4 1000 1
Sale1 25.01.2007 -5 -1200 1

After the settlement phase (with FIFO principle), we have the following picture:

Issue Receipt Settlement
Code Qty Original Cost Price Price Adjustment Code Qty Original Cost Price Price Adjustment Qty Amount Adjustment
Trsf1 -2 -480 220 40 Purch1 1 200 200 0 1 200 -40
Trsf2 2 480 240 0 1 240 0
Sale1 -5 -1200 240 40 1 240 0
Purch2 4 1000 250 0 4 1000 40
Trsf2 -2 -480 240 0 Trsf1 2 480 240 0 2 480 0

Take a notice that we are having a loop in our cost movement graph: the issue from the second transfer is settled to the receipt of the first transfer. Then, the receipt of the second transfer is dependant on the issue of the first transfer. Let’s have a look on what will happen on next iterations. I will specify an accumulated adjustment for the given issue/receipt in the adjustment field of the receipt or issue part of the table. Every new table below corresponds to another iteration of the inventory closing.

Iteration 1

Issue Receipt Settlement
Code Qty Original Cost Price Price Adjustment Code Qty Original Cost Price Price Adjustment Qty Amount Adjustment
Trsf1 -2 -480 220 40 Purch1 1 200 200 0 1 200 -40
Trsf2 2 480 240 0 1 240 0
Sale1 -5 -1200 240 -40 1 240 0
Purch2 4 1000 250 0 4 1000 40
Trsf2 -2 -480 220 40 Trsf1 2 480 220 -40 2 440 -40

Iteration 2

Issue Receipt Settlement
Code Qty Original Cost Price Price Adjustment Code Qty Original Cost Price Price Adjustment Qty Amount Adjustment
Trsf1 -2 -480 210 60 Purch1 1 200 200 0 1 200 -40
Trsf2 2 480 220 -40 1 220 -20
Sale1 -5 -1200 244 -20 1 220 -20
Purch2 4 1000 250 0 4 1000 40
Trsf2 -2 -480 255 -30 Trsf1 2 480 255 30 2 510 30

Iteration 3

Issue Receipt Settlement
Code Qty Original Cost Price Price Adjustment Code Qty Original Cost Price Price Adjustment Qty Amount Adjustment
Trsf1 -2 -480 210 60 Purch1 1 200 200 0 1 200 -40
Trsf2 2 480 220 -40 1 220 -20
Sale1 -5 -1200 244 -20 1 220 -20
Purch2 4 1000 250 0 4 1000 40
Trsf2 -2 -480 210 60 Trsf1 2 480 210 -60 2 420 -60

Iteration 4

Issue Receipt Settlement
Code Qty Original Cost Price Price Adjustment Code Qty Original Cost Price Price Adjustment Qty Amount Adjustment
Trsf1 -2 -480 205 70 Purch1 1 2 200 0 1 200 -40
Trsf2 2 480 210 -60 1 210 -30
Sale1 -5 -1200 242 -10 1 210 -30
Purch2 4 1000 250 0 4 1000 40
Trsf2 -2 -480 210 60 Trsf1 2 480 210 -60 2 420 -60

Iteration 5

Issue Receipt Settlement
Code Qty Original Cost Price Price Adjustment Code Qty Original Cost Price Price Adjustment Qty Amount Adjustment
Trsf1 -2 -480 205 70 Purch1 1 200 200 0 1 200 -40
Trsf2 2 480 210 -60 1 210 -30
Sale1 -5 -1200 242 -10 1 210 -30
Purch2 4 1000 250 0 4 1000 40
Trsf2 -2 -480 205 70 Trsf1 2 480 205 -70 2 410 -70

Iteration 6

Issue Receipt Settlement
Code Qty Original Cost Price Price Adjustment Code Qty Original Cost Price Price Adjustment Qty Amount Adjustment
Trsf1 -2 -480 202,5 75 Purch1 1 200 200 0 1 200 -40
Trsf2 2 480 205 -70 1 205 -35
Sale1 -5 -1200 239 5 1 205 -35
Purch2 4 1000 250 0 4 1000 40
Trsf2 -2 -480 205 70 Trsf1 2 480 205 -70 2 410 -70

And so on… If we had not  have the issue with the transfer of the item before actual the purchase of the item, then the inventory closing would finish in two iterations. In our example, the actual inventory closing will take around 5-10 iterations. Furthermore, We can estimate that the issue cost price for the sales order will not achieve the correct value of 1200 EUR and a difference will be written-off from transfer transactions as a rounding error.

So, should we have a negative inventory case for one of the time-points in a period of an inventory closing, this would lead to considerable increase in the number of iterations required for the inventory closing and to deviation of the calculated inventory cost from the actual cost.

Besides that, We should remember that for the weighted average model, as a most general principle, every issue is settled to every receipt. Thus, loops in a cost movement graph are natural for the weighted average model and happens even if inventory have not went negative in a period being closed. As of version 2009 it is still true. During an inventory closing every receipt is still being settled to every issue. Even if now it is made via a dummy transfer, not via a direct settlement, it does not change economical meaning of a weighed average inventory closing.

I should mention also, that a cost propagation does not propagate costs through inventory transactions which went through an ‘inventory on-hand adjustment’. If during the cost propagation the system runs into such a transaction, the system adjust it, but then – the system write the adjustment off to a rounding errors GL account, thus effectively stopping a cost propagation via the on-hand-adjusted transaction. It looks quite natural, for the any on-hand-adjusted receipt should be treated as a kind of  completely new entity, not connected with the inventory issue from some inventory transfer.

Because it is a frequent case when an inventory is being closed for some long and not completely defined period of time, there are two fields in inventory closing parameters, which allows to limit the number of iterations in the inventory closing:

  1. “Maximum throughputs”. This is exactly the field which holds the maximum number of iterations allowed. If during cost propagation, an inventory closing procedure has reached the iteration with the number equal to the number specified in this field, then the next adjustment (which came from the last iteration in inventCostListTrans table) would not be propagated to the corresponding issue transaction, but instead will be written-off to a rounding GL account immediately after being applied to the appropriate inventory receipt.
  2. “Minimum throughput adjustment”. If value of an adjustment for the single transaction, which came from the previous iteration,  is below the threshold specified in this field, then this adjustment is not propagated to the corresponding inventory issue, but is written-off to a rounding GL account immediately. I want to emphasize that this parameter stops cost propagation only for those ‘cost flows’ which falls under this threshold. E.g. if we have 3 adjustments from the previous iteration, say, 2,10 and 80 eurocents, and the value of the Minimum throughput threshold is set to 5 eurocents, then only the first of adjustments will be written-off to a rounding account (and the ‘cost flow’ will be stopped effectively). Remaining adjustments will be taken into the following iteration, and corresponding cost flows won’t be stopped.

Procedure for writing-off adjustments is described in more details in the section “On writing-off  rounding errors and adjustments during cost propagation“.

There is some odd feature of inventory propagation which often cause user’s confusion. There is a rather typical user’s question: “I had already closed inventory transaction, but it was somehow modified during the inventory closing. Why ?” The answer to this question consists from two points:

  1. An inventory transaction should fulfill to following conditions to be marked as closed: A quantity in the transaction should be equal to A settled quantity in the transaction(inventTrans.qty==inventTrans.settledQty); A cost price in transaction should be equal to a settled cost price in the transaction(inventTrans.costAmountPosted+inventTrans.costAmountAdjustment==inventTrans.costAmountSettled) (To be absolutely precise – they should not be equal. Actually – a difference between the values must be less then some defined delta).Thus, if cost of a closed inventory receipt was adjusted somehow (may be we just have manually adjusted it through a form, or a cost price was changed during a cost propagation), then this transaction is bound to be open immediately.
  2. Even before a receipt-to-issue settlement procedure, the system scans all open or partially settled receipt transactions, calculates the difference between a cost price and a settled cost, and should it has one – send the difference to the propagation routine. (It is performed by the inventCostItemDim.updateReceiptAdjustment() method, which is called BEFORE call to the method inventCostItemDim.updateModel() which is responsible for a receipt-to-issue settlement.

I was thinking – could this problem be solved with some different approach ? Perhaps, upon attempt to adjust a closed transaction, instead of pushing an adjustment value to the propagation routine, the system might just write-off the adjustment to a rounding account. Say, we allocate markups to a closed transaction with the posting  D Inventory    C Transportation costs 50EUR, and then immediately write this adjustment off with the posting D Rounding   C inventory 50EUR. Alas, this approach has some gross disadvantage: If our receipt transaction is closed already, it does not mean that the corresponding merchandize has already left our company. It could be simply moved to an another warehouse with a regular inventory transfer journal. Then,  the purchase transaction has been closed with the inventory issue of the transfer, but the receipt of the inventory transfer is still open and well. Now if are we trying to book transportation and custom clearance costs to an inventory (and this costs sometimes might be comparable to the purchase cost), with the approach described, these costs wouldn’t be saved into inventory costs as an adjustment, they actually would be immediately written-off to a rounding account. (Which probably would be closed to losses in the end of period). Hence, a marginal profit for the sales order which sells this merchandize would be unreasonable high, because markup costs was not included into a cost price of the merchandize. As a bottom line – it seems that there is no alternative for the current approach.

Finally – to close the theme I want to mention that Dynamics AX 2009 introduced the extra step for cost propagation –  an adjustment and calculation of indirect expenses for production orders. I will describe this later in the section “Cost breakdown and indirect costs“.

GL postings creation

First, I want to mention, that GL postings are created by an inventory closing only if the Ledger field has been checked in IC’s parameters. Personally – I see no point in un-checking this field, because a time required for creation of GL postings is insignificant, comparing to other steps of an inventory closing. In version 2009 this field is hidden from the parameters form and is always checked.

On previous stages of inventory closing, the system has generated and filled the lines of the inventory settlement table (inventSettlement), which, besides settled quantities, settled amounts and adjustments, contains information required to create GL postings from adjustments performed: a GL inventory account, a GL offset account, posting for inventory and offset GL accounts. So, GL posting creation procedure looks pretty simple. It just sums-up and group amounts from invent settlement by particular accounting parameters. Therefore, there is no necessity in some routine that would reconcile GL postings of an inventory closing to inventory transaction data. If the inventory closing has generated GL transactions with some odd account numbers or dimensions, try to find this dimensions and accounts in the settlement table. Then, from this table, one can drill-down to original inventory transactions and check them for suspicious data.

Furthermore, GL postings creation depends from the particular inventory closing parameter, called “Specification”. This parameter allows to group GL postings not only by accounting parameters (GL accounts,Dimensions, GL Postings), but also on itemId or item group of the item being adjusted.

NB: How does the system knows that for transactions which did not generates GL postings, the inventory adjustment also should not be posted into GL during the inventory closing? During the posting of the originating inventory transaction, the system stores into the inventTransPosting table the information on the inventory account, the offset account, the dimension and, more important, whatever the GL posting has been created for this transaction(isPosted). I want to mention that account and dimension information is always stored, even if transaction has not been posted to GL. Then, during the receipt-to-issue settlement and the adjustment calculation, еру system fetches  account and dimension information from the inventTransPosting table, but, If inventTransPosting.isPosted is set to No, then  the accounts, dimension and posting of inventSettlement would not be filled, which will cause this record to be skipped during GL posting creation. So – If you are trying to understand meaning of some odd GL postings and You are analyzing  a content of inventSettlement table, You can safely ignore any records with the Posting field set to zero (LedgerPostingType:None) because they are ignored during GL posting.

Besides that, in version 2009,  on the last stage of an inventory closing, the system calculates and post variations between standard and actual costs. I will look closer at this topic in the section “New standard cost

Multi-User Inventory Closing (MUIC)

A parallel inventory closing from several workstation was introduced in the version 3.0sp2 and has become the most significant improvement of an inventory costing in the history of Axapta. To my opinion, since this release, performance issues of an inventory closing became solvable, whilst in previous versions these issues had been being a real terminal problems…

So – Let’s look at a multi-user inventory closing in more details.

Client (I mean – Dynamics AX reach client process), which originally have started inventory closing, will be referred as a “master client”. Every other client will be referred as a “helper client”. Just to remind –  Helper client is started by click to the Calculation->Calculation Help menu in he inventory closing form, whilethe unfinished inventory closing is selected in the grid.

When the inventory closing procedure is started on the master client, it writes the user-specified parameters into the Inventory Closing table (inventClosing) to allow them to be fetched from helper clients. Then, master client writes the list of inventory items, which have open transactions, into Calculation List table (inventCostList). Then, master client, as well as helper clients, begin to perform the receipt-to-issue settlement procedure for items from this table (the routine described in Receipt-to-issue settlement section). This stage considered to be zero iteration. As I’ve already mentioned in Cost propagation section, during a settlement process, the system creates records in InventCostListTrans table, which will be used as an input data to cost propagation iterations. It is worth to mention that if some inventory item has been referenced from inventCostListTrans table, reference to the same inventory item is inserted into the inventCostList table. This is necessary for a coordination of work between multiple inventory closing clients during a cost propagation process.

The similar algorithm is also used on every cost propagation iteration. Every client runs through inventCostList records and for every inventory item referenced, it performs an iteration of the cost propagation, fetching adjustment data, which has came from the previous iteration, from the inventCostListTrans  table.

The elementary unit of work for both settlement and cost propagation steps is an inventory item. Settlements creation/iteration of cost propagation for an inventory item is always performed from one client.

Posting of IC results to General Ledger is performed from the master client only.

If  Calculation->Pause Calculation menu item is clicked during an inventory closing, then the system will suspend the procedure. The inventory closing session may be resumed little bit later on, by clicking to Calculation->Calculation Help. In this case, the first client which has joined the suspended closing session would became a master client.

If you are using more than 2-3 workstations in a MUIC, it is worth to check utilization of DB and AOS server’s resources. You should check both CPU and disk metrics. (I won’t discuss all necessary metrics in details here). If one of the metrics has achieved some 60-70% of reasonable maximum, one should stop adding new MUIC clients. Also, to my experience, it is a quite normal practice to start 2-3 MUIC clients form the same workstation, because most of the time they do not utilize local resources, but are waiting for a response from AOS or DB servers. It is worth to mention, that a settlement stage usually are bottlenecked by DB Server performance, not by the number of MUIC clients. Hence – Before start of a cost propagation stage, it is not worth to add too many clients to the inventory closing session.

Although MUIC designers intended inventCostList and inventCostListTrans tables to be used only as some internal mechanism for communication between MUIC clients, as a bonus side-effect, thess tables are a perfect tool for an IC monitoring and tracing. But before we proceed to some ideas on IC monitoring, let’s discuss in details the structure of these tables:

InventCostList

Field Description
ItemId Item number for the item to be processed in IC session
Voucher Voucher number for given IC session. It is used to lookup parameter data from inventClosing table by helper clients
CostNum, NumOfIteration Two fields with opposite meanings. NumOfIteration – number of the current cost propagation iteration; CostNum – field with the opposite meaning. Roughly Speaking – Maximum Number of Iterations minus Number of iteration

InventCostListTrans

Field Description
ItemId Item number to be processed by cost propagation
InventTransId Number of receipt inventory lot to be adjusted in iteration of cost propagation
InventTransIdReturn Number of returned inventory lot. Used if on sales order return (or more generally on any return) number of returned lot (inventory issue lot which is being returned) has been specified. This is special case, because adjustment value is ignored by cost propagation routine. Instead – Returning receipt lot cost price is set to the full cost price of issue inventory lot being returned (with reverse sign indeed)
VoucherPhysical Is used only for quarantine order and for the returns. Is necessary only to refer to originating inventory transaction, which is a precursor for ht current transaction.
Voucher Voucher number for given IC session. Is used to lookup parameter data from inventClosing table by helper clients
NumOfIteration Number of current cost propagation iteration. Is used for coordination between MUIC clients. It prevents the attempt of certain client to proceed with the next iteration, while the most of client still process the current iteration.
Adjstment Adjustment value itself.

A receipt-to-issue settlement stage considered to be the stage number zero. InventCostListTrans table does not contain records for this stage

In version 2009 technical infrastructure for MUIC has been changed significantly. First of all, this is caused by the transition to the new batch server technology. Although this topic is quite interesting by itself and deserves a separate article, I will briefly enumerate ideas behind the new batch server technology:

  1. In DAX 2009, batch task complying to the new batch server infrastructure, are performed on the AOS server, not on a dedicated workstation with the regular Axapta’s reach client. This was made not in intention to save customer’s $800 for a dedicated computer, but to support an environment for parallelization of computational intensive tasks. It is clear, that in mid-range prospect, the number of processor cores in a single processor would be quickly increased, while  performance of a separate core would increase for some tenth of percents for every doubling of cores per CPU. Hence, if we want to lay some groundwork for  further performance and scalability increase,  we should provide  support for multithreaded execution of the most computational intensive algorithms of Dynamics AX. It is more natural to base such  support on the server side (which is already multi-user and multi-threaded), than to develop it on the base of an old client-based batch server. An old batch server is still supported and used by some existing modules, but it is clear, that it will  become obsolete in the near future.
  2. New batch infrastructure supports the API for submitting a batch task into a batch queue (BatchHeader class). It provides means to specify dependencies between tasks or to spawn several tasks to be executed in a parallel.
  3. New batch infrastructure currently is supported only for a few especially computational intensive tasks. From the first sight, decent parallelization support is provided for an inventory closing and a MRP planning; more or less satisfactory support is provided for a GL journals posting; support for a sales order/purchase order posting parallelization is pretty rudimentary. (If I understood correctly, it works only in the case of posting of multiple invoices for multiple POs/SOs. It would not decrease the time required to post the ONE invoice for ONE sales/purchase order with large number of lines. From the other side – POs/SOs with several thousands of lines is a quite seldom occurrence; May be I just  too harsh on these changes to order posting).

In our particular case, to turn on the new parallelization mechanism for the inventory closing, we should specify  values for Helpers Batch Group and Extra Batch Helpers fields in Inventory module parameters. I can not provide an exact hint for setting the number of helper processes (I still have not used this mechanism in real implementations for now), but I would start with the number of cores on AOS’s CPU multiplied by two. This implies also, that the number of threads for a batch server specified in Administration->Setup->Server Configuration->Batch Server Schedule is greater or equal to the number of helper processes. Surely – If the same server is also utilized for some routine user operations,  we should specify a lower number of helper processes. The effective number of active helpers would be equal to a minimum between the value specified in the inventory module parameters form and the number of active threads specified for the given time interval in the batch server administration form.

The old user interface for starting/stopping helper processes remains in place, but it is used to start/stop helper threads on he batch server, not helper clients as in an old version.

By the way – In version 2009, a cancellation of inventory closing is also performed in parallel. Before that, some implementation (especially with weighted average costing being used) had a non-solvable issues with a cancellation of inventory closing. Due to usage of MUIC they had been performing an inventory closing in reasonable time, but since an IC cancellation had been performed in a single-client mode, it led to unreasonably long time period required to undo closing. Upgrade to version 2009 may become a good relief for this kind of issues.

How to monitor an inventory closing process ?

The most convenient way to monitor an inventory closing is just open another Axapta client and browse through the inventCostListTrans table with thr table browser after inventory closing has been started.

If you sort records by an iteration number, then you can instantly see – what iteration is processed currently. As a rule, if the implementation supports some more or less serious logistics, then inventory can not be closed with less than 5-7 iterations. If your inventory can be closed for the lesser time, then you probably is not interested in IC’s performance and can safely skip the rest of the section. During these 5-7 iterations, number of records in InventCostListTrans table is usually decreased from tens of thousands to several hundreds or even several tens. After these iterations has been finished, it is worth to look more closely on the content of this table. Perhaps-you have noticed several  inventory lots, for which amount of cost propagated (in the adjustment field) is being gradually decreased from iteration to iteration somewhat unusually slowly. Say, If the adjustment is 600EUR and it is decreased only for 10 eurocents with every couple of iterations – it is just about time to try to understand – whatever we have some issues with this inventory lot or not ?Most probably, the reason for such behavior is that inventory went negative for the given item in some point of time of the period being closed.

If you are a perfectionist or value of this hanging adjustment is rather high  (say – 6000 for the company with monthly revenue of 6000000), then one should write down the ids of lots being adjusted, stop the ongoing inventory closing, cancel it and begin to sort out the lots in question. If you have found some cases of negative inventory, then you have to accurately reverse these transactions, mark reversed transactions to reversing ones (or use returned lot number mechanism) and attempt to run another inventory closing for the same period. (Discussion about the marks and returned lot mechanism follows in the “On reversals” section).

If you  find that the hanging adjustment value is inconsiderable comparing to the period’s turnover (say – 50 for the company with the monthly revenue of 50000000), you can just adjust (in the table browser)  the value of MinTransferValue of the appropriate record in the InventClosing table. This is exactly the field which is used to store the value of the “Minimum Throughput Adjustment” inventory closing parameter. If you specify the value which is higher then any of remaining adjustments being propagated, then on the next iteration, all remaining adjustments would be written-off to to a rounding GL account and the cost propagation stage would finish. Usage of this technique would not compromise overall precision of company’s financial statements, although it will definitely lead to somewhat  inconsistent COGS/Marginal profit values for particular sale orders. So – the basic principle of this technique is the following:

  1. On the start up of inventory closing we specify some reasonably low value for “Minimum Throughput Adjustment” field (say 1EUR or even 0.50EUR)
  2. After inventory closing has been run for some 20-300 iterations, we change the value to something considerably high (say 100EUR).

As a result – inventory is closed with high precision for ‘correct’ inventory transactions and with tolerable precision for ‘incorrect’ ones.

That is why it is so important for estimated issue cost not to vary to much from true issue cost. If the cost propagation graph has loops and a propagation adjustment is being decreased too slowly, then every euro of the difference might cost and extra 5-10 iterations of an inventory closing.

Besides that, I advise you to develop some inventory checking functionality which would run through open inventory transactions and warn a user if  inventory went negative. If you were to  run this functionality before inventory closing, you would not wait for 20-30 iterations to find suspicious inventory transactions. I saw several implementation of this functionality developed by different partners. I do not know, why Microsoft still has not developed it. In version 4.0 some primitive form of an inventory checking was introduced (Close procedure->1.Check open quantities in Inventory Closing form), but it is worthless because it checks only a inventory on-hand quantity for the date of inventory closing and do not check it for every date of period being closed.

Finally, It is worth to mention that for some businesses, negative inventory is a quite regular case. E.G. if some company is selling merchandize ‘on-the-fly’, instantly upon arrival, it is quite natural to sell it and invoice it, several days before purchase invoice is arrived and booked. In this case the only way to improve IC performance is usage of MinTransferValue nudging technique I’ve described already. Sure, In this case COGS and Marginal profit for separate sales would be definitely inconsistent, but for these type of businesses, a cost analysis for separate sales order is usually pointless.

Inventory recalculation

Dynamics AX supports not only inventory closing procedure, which calculates a final issue cost price for every open inventory transaction of a period (blocking the period for further posting of inventory transaction), but also an inventory recalculation procedure. This procedure differs from the an inventory closing in the following way:

  1. Receipt-to-issue settlement is performed ‘in-memory’, that is  – no settlement records are written to the inventory settlement table.
  2. If a cost price need to be adjusted, an adjustment record is written to the  inventory settlement table. This record has the adjustment value filled-in, but settlement qty and settlement amount fields are left empty.
  3. As a consequence of first two points – the system performs ‘in-memory’ receipt-to-issue settlement before EVERY iteration of cost propagation. It looks quite logical, for if we do not have settlement data stored in a database, we have to rebuild one every time, just to know what issue must be corrected for the cost propagated to the given receipt.
  4. Recalculation may be entertained for the single item or item group. The only thing that needs to be remembered is that if given item have been issued for the BOM journal or production order, it might result in adjustment of an other item’s transactions, because in this case cost is propagated to other inventory items.
  5. During inventory recalculation, system does not write-off rounding.

If we think about the 3rd point more deeply, we can draw a conclusion that an inventory recalculation is ALWAYS slower than an inventory closing, because the settlement stage is performed many times. Surely – It can save some time by omitting  many inserts to the inventSettlement table, but probably – saved time will be overweight by the time spent for making a settlement.

Finally – I want to discuss the idea, that having an inventory recalculation run every day can  significantly improve performance of final inventory closing. Usually – this is not a case. Of course – in ideal world, if recalculation has been run every day, and there was no post factum posting of receipts and issues, then the inventory closing run would not result in many adjustments, and since we do not have so many adjustments, cost propagation would require not so many iteration to finish. But actually – in this ideal case, a simple inventory closing without preliminary recalculations also would have pretty decent performance. Usual case for low performance of IC is a negative inventory and loops in a cost graph, and this can not be resolved with just periodic inventory recalculations. So – inventory recalculation only meant to calculate realistic issue cost prices before the final inventory closing, not to improve the performance of an IC.

Cancellation of IC

Algorithm for IC’ cancellation is simple and quite obvious:

  1. The system runs through old inventory settlements of IC or inventory recalculation and marks them as cancelled.
  2. The system copies the old inventory settlements into new ones, reverting values of settled qty, settled amount and adjustment amount.
  3. The system subtract  values of the fields mentioned from corresponding fields of originating inventory transactions.
  4. New settlement records are posted using the same approach as described in GL postings creation section. Before version 2009, regular settlements and cancellation settlements were posted with the same class (same piece of code). In version 2009 this logic was split into separate classes to post a cancellation and to post regular operation. In version 2009 inventory closing procedure was overloaded with logic to perform a variation posting (for new standard cost mechanism) and a indirect cost posting. Thus result in separation of posting and reverse posting logic

It is also worth to mention that an IC cancellation procedure is used as well to cancel an inventory on-hand revaluation or an inventory transaction revaluation.

Per-Batch costing

There is no separate setting in Axapta to support a per-batch inventory costing. But if we set the “Financial inventory” flag for Batch or Serial inventory dimensions in inventory dimension groups, an inventory closing would calculate costs on per-batch or per-serial basis, regardless of the inventory model specified, because receipts and issues will be settled only inside a single batch (or serial). It is reasonable to set a model for inventory items with per-batch costing to a FIFO inventory model, because it has a fastest processing time.

An inventory lot as a basic unit of cost

An architecture of an inventory costing in Axapta implicitly assumes that an inventory lot is the most elementary, atomic unit of cost. So – even if we have several records of inventTrans table with the same lot id, they all are treated as one atomic element of a cost.

From the first sight, this approach seems to be quite logical, but there is also a catch. Say, we have some item with a per-batch costing (The Batch dimension is specified as a financial inventory dimension), and we have two batches – the first batch has 2 pieces for 10 EUR apiece, the second batch has 3 pieces for 15 EUR a piece.

Then – we create a transfer journal for 5 pieces. If the Batch inventory dimension has not been marked as a primary dimension, we can leave this dimension blank in a journal line. Then, during reservation, the system will automatically split issue and receipt inventory transactions for journal lines, substituting available batch numbers into every new line of the inventory transactions table. This is convenient for users, because it allows them to save time required to specify batch number, if they do not care about the batch number for this specific line of the journal.

But after this journal has posted, we will find that all receipt transactions have the same receipt price of 13 EUR. During the posting of the journal, the system use WHOLE LOT’s issue cost as a receipt cost.  The lot’s issue cost is 65EUR, then this 65EUR is evenly allocated to every receipt inventory transaction, thus  resulting in price of 13EUR apiece. An inventory closing won’t fix this problem, because adjustments for a cost propagation is processed in per-lot basis. Therefore, during inventory closing any adjustment would be again evenly allocated across all receipts. Such a case often lead to a lot of complaint about “Axapta broke my cost price”.

When I first met this issue, I tried to storm it by patching the transfer journal posting procedure and the inventory closing procedure. But it turned out, that the problem seems to be unsolvable for the general case. E.G. we can issue several batches in one transfer line and combine them into one new single batch. And what should we do if the transfer changes not only physical inventory dimensions (like WMS cell), but also financial ones ?How to establish the link from issuing sub-lot and receiving sub-lot ? And what, in most general case, this sub-lot is ? What is a most natural, easy comprehendible, atomic unit of cost ?

This case might also be an example of an attempt to resolve the business issue with an IT-approach. If we look into situation closer, we would run into a requirement conflict: From one side, somebody (most probable – peoples from accountancy/finances) insists on the per-batch costing. Probably, it implies that batch numbers are used to keep track of something (maybe – vendor of the item, may be some special discounted price from vendor, may be it is used to keep track the to-stock/on-order status of  an item etc). From other side – some other departments do not care at all about a batch number and trying to make the system to substitute these batch numbers for them. In this case – there are two possible solutions for the problem:

  1. Insist on a batch number to be specified by a user. The project sponsor should make a SEVERE EXPLANATION to end users about meaning of batch numbers for the company.
  2. If there is no some implied info associated with batch numbers, It might be useful to push the accountancy into usage of the FIFO costing and discontinuation of the batch number usage. If a batch number is used ONLY to track the exact source of an issue cost price, then it might be much more useful to just run an IC with FIFO model and develop some report which would show the originating purchase transaction for every issue transaction from  settlement data.

Inventory settlements and how to work with them

This section is dedicated to the inventory settlements table (inventSettlement). Strictly speaking, usage of word “Settlement” in the name of this table is misleading, because this table is used not only to store receipt-to-issue settlement information, but also for keeping a track of adjustment made for given inventory transaction. Although for issue inventory transactions, an adjustment is always calculated from settlement data, for receipt transactions an adjustment may be made completely independent from the settlement. Also, if an adjustment of an issue cost price is made during an inventory recalculation, then the settlement would be made ‘in memory’ and the settlement record would actually keep only the adjustment value, but not the settlement data. Let’s have a more closer look at the table’s fields:

Field Description
TransRecId Reference to the RecId of the inventory transaction being settled/adjusted
InventTransId Inventory lot being settled/adjusted
ItemId Item number of the inventory lot being settled/adjusted
ItemGroupId Item group of the inventory log being settled/adjusted
TransDate Date of settlement/adjustment
Voucher Voucher of the settlement/adjustment. Might be used to drill-down to originating inventory closing/markup posting/etc
SettleTransId Surrogate key. Actually – just gradually increased counter. Value of this field is used to fetch receipt-to-issue settlement info. Actually – settlement is stored as TWO settlement records – one for the issue and one for the receipt. This TWO records will share the same SetleTransId
QtySettled Settled quantity. If given record was created as an adjustment (not the settlement), this field is blank.
CostAmuntSettled Settled amount.If given record was created as an adjustment (not the settlement), this field is blank
CostAmountAdjustment Adjustment amount. Field is always not blank for adjustment records. For settlement records this field is has some value only if settlement has resulted in adjustment.
BalanceSheetAccount Inventory account for the adjustment.
OperationsAccount Offset account for adjustment
BalanceSheetPosting GL posting type for inventory side’s GL posting of adjustment
OperationsPosting GL posting type for offset side’s GL posting of adjustment
Dimension Dimension to be used during posting of the adjustment
Canceled Is True if given settlement was cancelled
SettleModel Settlement Model. Roughly speaking – is something like inventory model
Posted True if this record was processed by settlement GL posting procedure.
SettleType Settlement Type

The Receipt-to-issue settlement created by inventory closing, is stored in this table as a two records – one for the issue and one for the receipt. These two records share the same SettleTransId.

For adjustments – this table contain only one record per adjustment.

Fields related to GL posting are filled with information from the inventory posting table (InventTransPosting) linked to the inventory transaction being adjusted. If there no record in inventTransPosting has been found by some reason, then the system fetches the inventory account from the inventory issue/receipt settings of the  inventory posting setup and offset account – from the inventory profit/loss settings of the inventory posting setup. I should mention also, that the inventTransPosting record is filled during the financial update of the inventory transaction and data remains in this table even if a source inventory document (sales order/inventory journal/production order) has been deleted.

It is worth to mention, that an adjustment to an inventory transaction not always leads to creation of GL postings. Say – If adjustment for a quarantine order transaction was made, this should not lead to GL posting creation, because the originating quarantine order has been posted without GL postings creation. The system keeps track of whatever the original document was posted into GL in isPosted field. If this field was set to false during originating inventory document posting, then system does not fill account fields in the inventorySettlement table and these records are ignored by the GL posting creation subsystem. So, if you are reconciling the inventory settlements to GL, you can safely ignore records with blank account and posting fields. It is also worth to mention that the Posted field in the inventSettlement table does not related to actual posting of the record into the General Ledger. This field is just a flag, which is used to mark records processed by the GL posting creation routine.

A little bit on inventory transactions

It is worth to have a closer look to that fields of inventory transactions, that are related somehow to cost and inventory closing:

Field Description
CostAmountPosted Original receipt cost price for receipt transactions; Estimated issue cost price for issue transactions.
CostAmountAdjustment Adjustment value. Must be always equal to the sum of adjustment from inventSettlement records connected to this invntory transaction
CostAmountSettled Cost amount settled during IC. For closed inventory transactions – is equal to CostAmountPosted+CostAmountAdjustment
Qty Quantity in inventory units (not purchase or sales units)
QtySettled Quantity settled during IC.
CostAmountPhysical. Cost price for physical operations. (E.G – packing slip posting). For receipt transactions, It is  usually equal to the price fro purchase order line; For issue transactions – to the estimated issue cost price. Attempt to use the value of this field for any substantial reports is probably sign of bad design. This field only meant for the  ROUGH evaluation of potential cost prices of non-invoiced inventory transactions.
CostAmountStd Value of the standard cost used on posting. (Fixed receipt cost – in DAX2009). Is set to zero, if standard cost (fixed receipt cost) is not used for given item.
CostAmountOperations Cost amount posted to operations (not-inventory) account. Actually – only filled-in for purchase transactions of service items.
ValueOpen Closed/Open tag for an inventory transaction. Transaction is marked as closed if CostAmountSettled==CostAmountPosted+CostAmountAdjustment and abs(qty-qtySettled)<0.00000001
DateClosed When transaction is being marked as closed, system sets this field to the date of the last settlement made for the transaction.

Inventory operation reversal (return)

There are two completely different mechanisms to create returns:

  • To return inventory issue, the “returned lot number” mechanism must be used. This returned lot number may be specified in, for example, a sales order line or an inventory journal line. Then, this lot number is being copied to an inventory transaction (into inventTransIdReturn field). During an inventory closing,  for the transactions with this field set, system finds an originating (being returned) inventory transaction, fetches the original issue cost amount from it, and set cost price of receipt (return) to this value with the sign reversed.
  • To return inventory receipts, the “marked inventory” mechanism is used. This mechanism will be described in more details in the rest of the section.

So – The system allows to mark inventory receipts and inventory issues to each other. It means, that the value of an inventory lot number (value of inventTransId) of the first party is written to the inventRefransId field of the second party and vice versa. During an inventory closing or an inventory recalculation, such linked transactions go out of the regular settlement and cost propagation routine. Instead of that, these transactions are settled directly to each other regardless of the inventory model. Consequently – the issue cost price become exactly the same as receipt cost price of marked receipt. It is also worth to mention, that both during a marking and during an inventory closing/recalculation of marked transactions, the system also checks the consistency of financial inventory dimensions between the transactions. That is – If you set the batch number as a financial inventory dimension, an issue and receipt with different batch numbers would not be allowed to be marked to each other. Even if somehow (e.g. by direct write to inventTrans from developed code) this transaction become marked, the system will clear this marking during an inventory closing and just settle this transactions with the regular routine.

Transactions may be marked both before a financial update and after a financial update. It is not allowed to mark an open transaction to a closed one, because during an inventory closing/recalculation this closed transactions are ignored, thus voiding the very idea of marking.

Unfortunately, besides cost calculation, marking in Dynamics AX is also used for some other purposes:

  1. On reservation in ordered, marking allows to link this reserved issue transaction toa  particular receipt transaction in “Ordered” status. Then – on physical update to this receipt transaction, exactly this linked issue transaction would be updated to “Reserved” status. So – We can associate our “Reserve in ordered” to some specific estimated receipt.
  2. In MRP, marking is used to store the information about covered/covering requirements. Roughly speaking, if two inventory transactions are marked, then corresponding records in Net Requirements (reqTrans) would be considered as covered/covering. Respectively, if we are firming planned order created from Net Requirement, then receipt inventory transactions created would have marked to covered issue inventory transactions.

It is worth to remember about these mechanisms, because often markings created from reservation or MRP planning, results in broken inventory costs.  If too much of inventory transactions have been omitted from the regular costing routine because of markings, costing would effectively run in the per-batch costing mode, thus ignoring an inventory model settings for an item.

Writing off  rounding errors and adjustments during cost propagation

Consider the following situation: We purchased 3 pieces of a merchandize for 10EUR. Then every piece has been sold (in separate sales orders). Say, we had not had any operations other than the purchase and sales. After an inventory closing, the purchase cost is distributed evenly between issue transactions, thus resulting in the issue cost price of 3.33EUR per transaction. Receipt transaction is much more interesting case. From one side, the settled quantity is 3 pcs (Whole receipt is issued and settled; Receipt must be closed). From the other side –  The settled amount is 9.99EUR, thus meaning that the receipt still can not be marked as closed, for the receipt cost price has open balance of 0.01EUR. In such a situation, the system will just revaluate the receipt, writing-off remaining monetary balance to the receipt offset account a profit and loss account from inventory posting setup. (It is worth to mention that a receipt offset account for the purchases is a purchase consumption account from inventory posting setup). That is, after an inventory closing, our receipt inventTrans will have original the cost price of 10EUR in costAmountPosted field and -0.01 of the rounding error in costAmountAdjustment field, and transaction will be marked as closed.

If the inventory closing is about to stop cost propagation (either because maximum number of iterations has been exceed, or minimum throughput adjustment has been achieved) it uses the same approach. Kind of – We received some adjustment from the previous iteration, we adjusted the receipt. Now we normally would apply this adjustment to settled issue transactions. But we won’t do it, because we are due to finish cost propagation. So – To avoid misbalance between the receipt and the settled issue, we just revaluating the receipt to the value of adjustment. Now, when we finally do not have an adjustment to propagate we can safely remove this cost flow from further iterations…

Update: After very initial version of this article was published, I looked into rounding mechanism of version 4.0 and found the following picture: In version 4.0sp1 method initInventSettlement of inventCostItemDim class, has new parameter added (_errorAdjustment). This parameter is set to true if this method is called from the createErrorAdjustment method, which, in turn, is implementing this rounding errors mechanism. It turned out, that service pack 1 introduced changes to the handling of rounding errors: If the parameter mentioned is set to true, then the system uses the profit and loss account from the inventory posting settings as an offset account for an adjustment, not an offset account from the InventTransPosting of the receipt transaction as usual. Before this change, all rounding errors were not, actually, written-off to dedicated GL account, but was hanging indefinitely on the offset account of the original receipt. If you still use any version older than 4.0sp1, I recommend you to backport this change to your version. Furthermore, I consider idea of writing off rounding errors to a P/L account as somewhat dubious. It would be much more convenient for an end-of-period reconciliation, to write-off rounding errors to some dedicated GL account, and then close this account to either profits or loss accounts during an end-of-period balance reformation. To achieve the required effect,  you need to modify method inventAdj::errorAcountOperation(). I would use variation account from standard cost setup for this purpose. If we actually use a standard cost mechanism for some items, then this is very account intended for all receipt cost variations; If we do not use standard cost, we can just adopt this existing settings field for our own logic. If this scheme fits you, You would just change the values of InventAccountType::InventProfit and  InventAccountType::InventLoss onto InventAccountType::InventStdProfit and InventAccountType::InventStdLoss respectively, in the inventAdj::errorAcountOperation() method.

Standard cost

Some people thinks, that an inventory closing just ignores  inventory items having standard cost mode checked in inventory model. This is not a case.  Moreover, if standard cost value of an item has changed recently, then the inventory closing for e.g the weighted average inventory model, would average the cost of old receipts (made on old standard cost) and the cost of new receipts (made with new standard cost). That’s why it is highly recommended (not because of some deep technical issues of a standard cost in DAX, but just as a good implementation practice), just revaluate on-hand inventory for standard cost based items, to the new standard cost value multiplied by an on-hand quantity, if standard cost value is about to be changed.

Besides that, I want to mention, that when the system write-off a difference between a standard cost and a purchase cost to a variation account, it proceed with the standard transaction revaluation procedure  (It creates the adjustment record in inventSettlement and writies the adjustment value to costAmountAdjustment field). Thus, after an initial financial update of inventory receipt, the field CostAmountPosted will contain the original purchase cost,  the field costAmoutStd – the standard cost value and the field costAmountAdjustment will contain the variation between the standard cost value and the purchase cost.

DAX2009 supports a completely new way of work with a standard cost price. The model, I describe here, is renamed to “Fixed Receipt Cost”.

Inventory closing for service items

I should make some preliminary point, that Dynamics AX has ambiguous model for setting up service purchase/sales:

  1. There is a special inventory item type “Service Item”. No records in inventory on-hand table (inventSum) are created for items of this type; These items can not be reserved; Generally – the system prohibits all operations which can not be performed for a non-stockable item.
  2. There is a check box “Post financial inventory” in an inventory model setup. If this check box is unchecked (which is as case for 99% of service items),then on a financial update of an item’s purchase, the cost is booked to a cost account (Purchase consumption account) instead of an inventory account as usual. (By the way – the cost amount is in this case is written to the costAmountOperations  field of instead of the costAmountPosted field). On any inventory operation other than a financial update of purchase, no GL posting is created at all.

An inventory closing for service items is simply meaningless. Actually, an inventory closing is necessary to calculate exact amount of costs transferred from inventory accounts to cost (COGS particularly) accounts. If we already have booked some item’s purchase expenses directly to cost accounts, what is the point in performing an  inventory closing for this item ? Why should we spend time and resources for the inventory settlement if we aren’t going to calculate an issue cost price from this settlement? That’s why, starting from version 4.0sp1, service items was excluded from the regular inventory closing routine. For all service item transactions, an inventory closing just sets transaction’s settled qty to total qty, closes transaction, and creates the special settlement for this transaction, which has a “service item settlement” model in SettlementModel field.

It is worth to remember about this new feature if you are upgrading from previous version and you have checked the “Post financial inventory” checkbox for service items in the old version’s implementation.

Why does negative inventory occur from time to time ?

Probably everybody (even a fresh newbie) knows that Axapta has a mode, which allows to sell some inventory item before a corresponding purchase was posted (Negative physical inventory and  Negative financial inventory checkboxes in inventory model).Although, from time to time this mode is turned on (e.g. for service items), generally this mode is turned off for the most of inventory items. That’s why newbie consultants become so confused, when they find that, despite a turned-off negative inventory mode, inventory, somehow, went negative for some time periods.

It is caused by the fact, that during physical or financial update of an inventory transaction, Axapta checks only CURRENT inventory on hand. And if some merchandize arrived yesterday and we updating the issue on date before yesterday, then the system allows us to do it.

Why it was designed that way ? In one of my projects, I redesigned and redeveloped the negative inventory check for financial update of inventory transactions. I had to write the code that checks an inventory quantity for the date of the issue, and then runs through all inventory operations for the given item and combination of inventory dimensions AFTER the date of operation, to check inventory to become negative. If an inventory issue was made on the current date, then this check was performed only slightly slower then regular check. But if operation was performed on the date, which was some 20-30 days from the current – a delay was quite noticeable, around +40-50% to time of a regular inventory update. Moreover, this check also produced a lot of locks for inventory on-hand data. But when I tried to develop the similar check for a physical update of inventory transaction, I ran into the much more serious problem. It was not clear for me- How should I implement the check for a physical update of an inventory transaction in Reserved status. It might happen, that a sales manager reserved some merchandize TODAY, but the system prevents it from being sold post factum, because there had been no enough inventory on hand for the date used. So – In this project I made a new negative inventory check for financial operations, but for physical operations, I left standard check intact. Since post factum posting was a quite seldom occurrence at this implementation, this approach did not lead to some serious problems. But I think it is not reliable enough to be implemented as a standard solution.

As a conclusion – I am afraid that there is no way to provide full support for a negative inventory check on every inventory issue. I think – actually Microsoft should provide the users with some report, which would periodically check open inventory for the occurrence of negative inventory in an open period (from the last inventory closing to now).

Instant estimated cost of an issue.

As a most general case, the instant estimated cost price is an instant average cost. That is – the current monetary on-hand value for the given item and given combination of financial inventory dimension is divided by the current quantity on-hand. This calculation take into account only financially updated transactions (roughly speaking – invoiced inventory transactions).

Generally speaking, degree of accuracy for instant cost price calculation is not very critical. If  a cost movement graph does not contain loops, there is no dependency between an estimated/actual cost variation and a time required  for inventory closing. But if the cost movement graph does contain loops, then every additional percent of an estimated/actual cost variation would lead to additional cost propagation iterations. If our inventory has the same item with costs of 400,600 and 700 EUR, and once we have written-off the item with the cost price of 1200EUR, probably, (even with loops in cost movement graphs) increase in inventory closing time would not be drastically increased. But if an item is written-off with the instant cost price of 100000000EUR, then we definitely will have a problem with inventory closing performance. Moreover, if we will use some unreasonably high estimated cost price, our monetary value of inventory on hand would go negative and we would face the situation when in inventory on-hand data we having 3 pieces of the item, with total cost of -600EUR. This situation (if happened) can be relieved with an inventory recalculation or closing.

Now we should discuss several special cases. If Include Physical Value checkbox is checked in inventory model setup, then quantities and amounts from purchase packing slips are also included into an instant average cost calculation. If there is a considerable lag between time of purchase packing slip posting and invoicing, then turning this mode on, might improve the accuracy of instant average calculation.

From some Axapta Sp3 to at least Dynamics AX4.0sp1, this include-physical-value functionality had a nasty bug, which sometimes led to insanely high values of estimated issue cost prices (billions and trillions). So – Be advised against usage of this feature if you have a version/service pack released before summer of 2008.

If during instant average calculation it turns out that inventory on hand does not have an item in question, the system uses the price from inventory table (item details->price discount->base price->price) instead of an instant average. The same value is used for items with a standard cost model. (Renamed to Fixed Receipt Price model in DAX2009).

If system calculates the cost price for a marked issue inventory transaction, it actually calculates the cost price of the marked receipt transaction, and then uses it with reversed sign. This approach decreases an estimated/actual cost price variation for marked transactions.

“Odd postings” on purchase return

From newbie consultants I quite often hear the complaint about ‘odd GL postings’ on invoicing of the purchase return. Something like – “Why I’ve got D Inventory C Accounts Payable 1400EUR on invoicing a purchase, and D Accounts Payable C Inventory 1400EUR; D Other Expenses C Inventory 200EUR on invoicing the purchase return?”

Here is the reason for the case: We must post the return amount (1400EUR) to Accounts Payable account. From the other side, if inventory marking is not the case, both estimated and true issue cost prices might differs from the return amount. (In our case – estimated cost is 1200 EUR). DAX tries to resolve the problem with the following approach:

  1. Return amount (return price multiplied by the quantity) is posted to Debit of Accounts payable
  2. Estimated issue cost price  is posted to Credit of Inventory
  3. Variation from return amount and estimated issue cost price is posted to Consumption account (specified as a Purchase consumption account in inventory posting setup).

In an end of accounting period, after inventory has been closed, this consumption account contains kind of ‘Financial Result for Purchase Returns”. E.G If we managed to return an outdated merchandize to a supplier with the return prices being higher than cost price, then Consumption account would have negative balance (Profit). If it is not a case – then it would have positive balance (Loss). In the end of accounting period this account would be closed to Other Gains/Other Losses account.

Moreover, if after posting of returns, we have marked them to the transactions being returned, and return prices had not been different from original purchase prices, then after inventory closing, we would have the zero balance on the consumption account, because adjustments made during the IC would reverse the postings to the consumption account.

Physically updated inventory transactions in IC

Although I have not written this before, but usually only financially updated inventory transactions are processed by an inventory closing. (I mean – inventory transactions in Sold or Purchased statuses, turned into these statuses during an invoice posting, inventory journal posting and so on). It is quite reasonable, because during a physical update, an actual cost price of receipt is not known, a physical issue transaction can be returned without being invoiced and so on. That is why, before Axapta 3.0sp2, physically updated inventory transactions were completely ignored by an IC process.

But starting from Axapta 3.0sp2, physical inventory transactions are included into an inventory closing process, but only as a second-class citizens. IC take physically updated transactions into account ONLY IF  the inventory item has  ‘Include Physical Value’ mode checked in inventory model group. Here is a main differences in costing process for these transactions:

  1. These transactions are not being firmly settled during inventory closing. I mean – They are settled between each other and with regular (financially updated) transactions but only in memory. No regular settlement record is made in InventSettlement if one of the settled transactions is physically updated.
  2. An adjustment might be made for the settled physical issue transaction. This adjustment is added directly to the costAmountPhysical field (not to the costAmountAdjustment as for financial transactions). The InventSettlement record for this adjustment has the special settlement model – Physical Value
  3. On the financial update of physical inventory transactions adjusted by IC, ADJUSTED amount is being reversed.
  4. Inventory transactions is included into inventory closing, deciding from a physical update date, not financial one.  So – If you have a transaction, which was physical updated on 31.09 and financially updated on 03.10, then this transaction would be included into IC for September.

I’ve met quite dubious results of attempt to use this mode on actual implementations. First of all – usually nobody cares about the date of physical update (packing slip update). I witnessed the cases, where inventory transaction  was updated with financial date, which was earlier than physical date. Kind of – Merchandise was processed by accountancy about 10 days before it has actually arrived to warehouse. The second – If by some reason purchase packing slip has not been invoiced before end of period, then quite regular sales invoice will be skipped from regular costing and settlement in the current reporting period, but, later on, it will be unexpectedly settled to some out-of-order receipt transaction. All the accountants would be very suspicious, why the August sale was settled to the December’s purchase. 🙂

This feature is not bad or good by itself. It can be turned on without any problems, but You have to guarantee that :

  1. Physical and financial posting dates agree to each other. Financial update date must be greater or equal than physical update date.
  2. It is Ok to have unposted purchase invoices during an inventory recalculation. But before an end-of-period period closing, all the purchases must be invoiced.

New standard cost support

Generally speaking, DAX2009 has introduced the considerable array of changes and extensions to the costing functionality, primarily aimed at providing a full support of an IAS-compliant standard cost inventory valuation. Some of these changes also have impact on the features, which do not related to the standard costing, but I am going to discuss all these features in a single section, to prevent the information from being scattered across the article.

Honestly speaking, I have not actually used new features in a real implementation, so most information was gathered from the X++ source code analysis. It is quite possible that I have mistaken in some details. Treat this article as a concepts guide, not as a detailed user guide.

Before proceed any further, I want to mention that old standard costing mechanisms are still in place and can be turned on by checking “Fixed receipt cost price” checkbox in Inventory Model groups setup. New standard cost functionality is turned on by specifying the “Standard cost” value in the “Inventory Model” field of Inventory Model groups setup.

Support for standard cost value history

DAX2009 has the history of standard cost values implemented. To look at or change the value of the standard cost for an item, the Price button in the inventory details form should be pressed. There are two tabs on the form appeared: The left contains planned, but still not active prices (itemPriceSim table). The right tab contains the already activated standard cost values (ItemPrice table). It is not possible to enter a value into the right tab directly. First, the new price should be entered into itemPriceSim table, and only after that, it might be activated by pressing the particular button. For a BOM inventory item direct editing of cost prices is disabled even in the simulation tab. We will discuss reason for that in the next topic, which will discuss standard cost variations.

I am glad to mention that activation procedure not only changes the value of standard cost in the history table, but also revaluates inventory on hand to the new cost value. Moreover, it is worth to list following facts on the new standard cost mechanism:

 

  1. In case of post-factum posting of an inventory receipt, the standard cost which was active for the receipt date is used. 
  2. In case of post-factum posting of an inventory receipt, this receipt goes through the full chain of cost changes, if new cost values have been activated after the posting date. Say, In March we had a standard cost for an item of 70EUR.From the 1 April it has changed to 75EUR. If in an Aril we are posting the item receipt with the date of March 26, it will be received with the cost of 70EUR, then automatically revaluated to 75EUR (with revaluation date 1 of April). It is somewhat untypical for Dynamics AX, but the system will use voucher number of the original receipt (say – a purchase invoice), while the voucher date will be equal to the evaluation date. Although system has provided support for a multiple dates per single voucher number since version 3.0, actual usage of this feature seems to be infrequent.
  3. The new standard costing is supported for physical inventory transactions. That is – after posting of a purchase packing slip, the value of costAmountPhysical field will be calculated from a standard cost, not from the cost specified in a purchase line. NB: AFTER a financial update is posted, adjustments for physical costs are cancelled. That is – before a purchase invoice posting, costAmountPhsycal contains a standard cost value, after invoice posting – just a cost from the purchase line. Moreover, some standard cost internals calculate a variance as a difference between financial and physical cost prices in an inventory transaction. (costAmountPosted+costAmountAdjustment-costAmountPhysical)
  4. After every financial inventory operation, an inventory on-hand value is checked for validity (invent on-hand value must be equal to an on-hand quantity multiplied by an active standard cost). If this is not a case, the system proceeds with an automatic invent on-hand value adjustment. This adjustment is not spread across all open inventory transactions as usual, but is linked to the last updated transaction. Besides, because of  performance limitations, an on-hand value is checked for the current date, and not for the date of the operation. I beleive, it is not significant, because in case of a standard cost, a rounding error which has occurred in a previous period, definitely would lead to the occurrence of the same error in a current period.
  5. A manual invent on-hand value adjustment and an inventory transactions adjustment is disabled for the inventory items with the new standard cost inventory model. Moreover, these inventory items are not processed by a regular inventory closing procedure (receipt-to-issue settlement and cost propagation). However, if standard cost item has been assembled from a regular items (having e.g FIFO as a costing principle), then the system will automatically post standard cost variations resulting from he cost propagation routine.
  6. All the inventory cost adjustments made for variations, rounding errors, automatic stock revaluation made because of standard cost value change, are still made with usage of inventSettlement mechanism. 

Variation accounting

I’ve witnessed usage of  standard costing  on my projects several times. Usually, a customer has described principles for choosing this costing model with following wording: “We have a complex production cycle, it would be hard to use FIFO or average, that is why we use standard cost. And speaking off-the-record, our production management is a complete mess, our cost accounting group is completely disconnected from production management group. We do not want to sort out this mess, that is why we use and will use standard costing.” Sure, I am exaggerating a little, but anyway most of enterprises use standard costing only to simplify accounting procedure. Actually, standard costing idea has nothing to do with simplicity of the accounting procedure, but is based on two simple assumptions:

  1. Only economically justified costs are included into cost price. That is – costs that are necessary and inevitable to produce a given product with required quality. Since it would be very time consuming to check the justification of every cost included into every batch of a production, some kind of cost rationing for the next economic period is implemented.  As a good practice, this rationing must be a part of  a budgeting process.
  2. Variations of the cost must be analyzed by a variation kind and by a variation responsible. A principle for the variation analysis is a industry-dependent. I prefer to intake medications made according to GMP and without variations at all. But if fertilizer for my home plants would contain a couple of percent more percents of potassium than it should – I would not care.

Complete review of standard costing goes beyond a scope of the article. If you are interested in the matter – try to read Management and Cost Accounting by Colin Drury. For the further consideration we only need to know the purpose of establishing the support for separate booking of different variation kinds.

If we look closer on the variation kinds posting setup, from the first sight, the meaning of only one posting type is clear: Purchase Price Variance. It is the same variance between a purchase cost and a standard cost which was supported in previous versions of Dynamics AX. Lets review other kinds of variations:

Inventory cost revaluation – an offset account for inventory on-hand revaluation on standard cost change. (Sure – It is not variation from the strict economical point of view, but it is quite understandable, why this setting is placed with settings for regular variations).

Inventory Cost Change – this kind of variance is more or less similar to Purchase Price Variance. Works for all types of receipts, other than Purchase. Also – This variation kind is used to book variations resulting from an attempt to revaluate receipt during an inventory closing/recalculation.

Rounding Variance – During a  variations calculation, these variations are calculated with unrestricted precision (actually – limited only to precision of real data type in Dynamics AX). But during posting, these variations are rounded to the currency round-off precision. Designers of the new standard costing mechanism decided not to rely on the standard rounding mechanism of Dynamics AX, which rounds an amount during posting to GL. Instead, they have implemented dedicated rounding mechanism for standard cost variations. (If this were not be a case, amounts from GL postings wound not match data from variations table (inventCostTransVariance). (Please, mention the difference between this rounding variance and rounding postings from invent on-hand rounding which guarantee that invent on-hand value is equal to standard cost value multiplied by on-hand quantity).

Much more interesting matter is a production variance (I suppose these was the main cause for the new standard cost implementation):

Production Price Variance – variance resulting from the difference between the planned and actual costs of one unit of material or work, consumed during a production. Even if our production has consumed the larger QUANTITY of units of work or materials, this variation would not occur.

Production Quantity Variance – variation resulting from the difference between the planned and actual quantities of the material or work consumed during production. This variance is calculated as a variance in the quantity multiplied by a planned cost of one unit of work/material.

Production Lot Variance – it is a little bit odd for me kind of variance related to the batch size of production consumption. DAX supports constant cost of consumption of material on a production route. (I believe – This is a kind of logistical expenses required to deliver material from warehouse to production spot, which in first approximation does not depends on the batch size). Suppose we  planned to consume 20 pieces of a material and consume 30EUR of related constant expenses. (1.5EUR a piece). Actually,  we have consumed 22 pieces. This results in variance of 30* 22/20 – 30=3EUR. So, Basically speaking, this kind of variance is related to consumption overheads, not to consumption itself.

Substitution Variance – All other kinds of variance. From economical point of view – variation from substitution of materials or route operations.

The next logical question is how the system calculates these variations ? What should be considered as a model of consumption quantity, unit of material/work price and so on ? The answer is quite simple – BOM calculation results are considered as a model. That is – during the last calculation of the BOM, system has saved the model data on quantities and prices of material/work consumed into BOMCalcTrans table. Thus, to calculate variations,  it is enough to compare model consumption data from BOMCalcTrans to actual consumption data in ProdCalcTrans (Production order calculation results). The body of variance calculation logic is contained in ProdStandardVariance class. The most interesting methods of the class are findOrCreateVarianceTrans(), which fills a variations table, finding the difference between planned and actual consumption, and calcVariance() which calculate variances themselves.

Now it is becoming more clear, why manual editing of price in ItemPriceSim table is prohibited for BOMs. Imagine that we calculated the BOM, got planned cost of 950EUR and manually updated it to 1200EUR afterwards. Now, if we have an actual cost of, say, 1100EUR, it would be quite unclear how to allocate -100EUR of variance, basing on data which says that variance is +150EUR…

Variation roll-up

As I’ve said already, Dynamics AX supports 4 kinds of production variation. Experience shows that this level of details is not sufficient for a real life variation analysis. Some mean for more fine-grained setup for variation accounting must exists. This mean is provided by the new Cost Group feature of DAX2009 (Inventory Management->Setup-> Bill of Materials->Cost Groups). By now, only three fields from this table (Cost Group, Name and Cost Group Type) have some interest for us. The groups having a Direct Material cost group type can be associated with an inventory item. The groups having f Direct Manufacturing cost group type can be associated with a Route Cost Category (Other cost group types will be discussed later). At the moment of Production or BOM calculation, cost group data from an item or an operation consumed is saved to the corresponding line of calculation results. When system is calculating the variations (in ProdStandardVariance class), cost group data is also copied into the temporary table with variation data. Since variation posting setup allows to associate GL accounts to a variation kind+cost group combination, the system uses data from this table to define actual GL accounts to be used during posting.

Finally, to complete the discussion on variations and standard costing, we should discuss a standard form used to analyze standard cost transactions (Inventory Management->Inquiries->Transactions->Standard Cost Transactions.) The topmost part of the form displays content of inventCostTrans table. It seems that this table was invented as a mean to separate cost movements from inventory operations. In most cases, this table is updated on physical or financial update of inventory transaction or on attempt to revaluate inventory transactions (which immediately rendered void by writing-off revaluation to cost variations). But sometimes, records are inserted to this table regardless of any inventory operation. E.G. on change to standard cost, a lot of record is written to this table. A half of these records is kinda writing-off old costs. Another half – is kinda accepting new costs. As of now – I see no much sense in this table. Most of the information in it is duplicated from inventory transactions; I have no idea how a similar mechanism would be implemented for non-standard cost based inventory models; Most of the functionality still uses monetary amount fields from inventory transactions; etc. It seems to me, that this table is wrong (or at least – unfinished) step into right direction. In the bottom part of the form there is a grid with data from a much more interesting table (InventCostTransVariance). This table is used to store detailed information on cost variations for a inventory operation (Basically it is a persistent variance roll-up data I’ve mentioned already). This info is stored only if the field Cost Breakdown of the Inventory Parameters form is set to Sub Ledger. The parameter Variance To Standard allows to set the level of details for persistent variance roll-up – Variation Kind or Variation Kind+Cost Group.

And finally – The very last question on new standard costing. Because inventory items with the new inventory model now bypasses regular inventory closing procedure, may be it is not worth to close inventory at all (Of course if all inventory items use the standard cost model) ? The answer is  NO! Although the regular inventory closing routine is bypassed for a new standard cost item, other parts of the inventory closing procedure still make some useful job for new standard cost items. In the end of the inventory closing procedure run, the system fills the inventCostTransSum table with summarized values from the inventCostTrans table up to an inventory closing date. Data from this table allows to speed-up a standard cost change value procedure and lower a time required to build some standard cost related reports. Thus, If inventory has not been closed for a long period, performance of these procedures can deteriorate considerably.

Cost breakdown and indirect costs

In this section we will discuss interesting, promising, but a little bit unfinished feature of Dynamics AX 2009 – a Costing Sheet. Settings for this feature are located in the Inventory Management->Setup->Bill Of Material->Costing Sheet Setup form. In the first approximation, it is a tree of a cost breakdown with cost groups located on a leaf level (The very same cost groups which are used for variation posting setup). Branches of the cost breakdown tree are used for more high level grouping of the costs. If we would play with the an addition of cost groups to the leaf level, we find two new interesting cost types – Indirect Surcharge and Indirect Rate. The first one is a cost calculated as a percent from the value of some other cost node in costing sheet. The second one is some fixed rate multiplied by some ‘cost quantity’. This ‘cost quantity’ are a summed-up quantities from a BOM calculation/Product order calculation belonging to the  specified node in costing sheet tree or to one of it subnodes.

Indirect cost calculation principles specified in costing sheet setup are used to calculate a production cost. As for direct production costs, there are two pair of GL account for every indirect cost kind: The first pair  is used during posting of picking list, route card/job card – i.e. WIP consumption journals. The second pair is used during final calculation of production order. Also, indirect production costs are posted to prodIndirectTrans table, introduced in DAX2009.

Since indirect costs may depend from a cost of consumed materials, it is clear that they must be also recalculated and updated during inventory closing/recalculation. Thus, If during inventory closing iteration some production issue transactions has been updated, the system proceeds (in inventCostItemDim.updateIndirectCosts() method) with the indirect costs recalculation. If these indirect costs actually were updated, then the system updates prodIndirectTrans data and push the indirect costs amount into the cost propagation routine. Also the standard inventory settlement posting routine was updated to support posting of newly created prodIndirectTrans records.

During an BOM/Production order calculation, the system also creates calculation result lines with indirect costs. And since calculation data are used for standard cost variation calculation, it means that a variation might also occurre from a difference in indirect costs…

Now – Why do I consider this feature as unfinished. It is obvious that any more or less complex production and production costing scheme would require to support different costing sheets for different inventory items. If we would look closer on Dynamics AX internals, we find that actually the system has some prototype support for existence of several costing sheets in parallel. But it is only a prototype as of now. I suppose the more full-fledged implementation of costing sheet will be implemented in future versions of Dynamics AX.

Join the Conversation

181 Comments

  1. Excellent article – Thanks for posting and keep up the great work

  2. Extremely helpful, thank you for posting this resource. It is by far the most detailed explanation of inventory closing i have seen thus far. This article has saved me SO MANY TIMES trying to explain AX behavior for auditors and accountants. I have sent this to some people in dev and support at Microsoft as an example of how their documentation needs to be improved.

  3. Hello,

    We use Avarege weightd model in inventory closing.
    When I try to close inventory, I receive an error message that some transactions should not be posted to the GL account without a department dimension value (null)

    I realize that as you write in your beautiful paper, The IC generates some lines in inventTrans table which has transtype = summedup. But these lines does not have a department dimension when I check from InventSettlement Table. Cause the gl accounts should be set as department dimension mandatory, how should I do to make the departments has a value.

    1. Well Fatih bey, Normally this summed-up inventory transactions (dummy transfers) are not posted to GL. You can check that related inventTransPosting record has IsPosted==NoYes::No and related inventSettlements has balanceSheetPosting and OperationsPosting set to None.
      I think, that on earlier stages of system implementation, You forgot to turn on mandatory check of Department dimension for certain account. As a result – You have some InventTransPosting records with EMPTY department dimension and Your GL account in either BalanceSheetAccount or OffsetAccount. Then, during inventory settlement phase, this dimension is copied into inventSettlement and Inventory Closing then chokes on attempt to post inventSettlement to General Ledger. The quick fix for your issue is to write the job which will iterate through inventTransPosting records with incorrect department value and fix them with reasonable default Department. It will look like the following pseudocode:
      ttsbegin;
      while select forupdate inventTransPosting
      where isPosted==NoYes::Yes && (BalanceAccount==’account in question’ || offsetAccount==’account in question’) && !dimension[SysDimensions::Department-1]
      {
      inventTransPosting.Dimension[SysDimensions::Department-1]=InventTable::find(inventTransPosting.itemId).Dimension[SysDimensions::Department-1];
      inventTransPosting.update()
      }

      ttscommit;
      Sure – It is only pseudo-code, It needs, at least a check to prevent update of InventTransPosting for already closed inventory transactions, but I think it is enough to get the whole idea.

  4. Hello Fedetenko,

    Thanks for your perfect document and your answer, We have a problem that we should not update all emty records in department field at inventtransposting. We just will try to enter the line in inventtransposting which is generated by the line in inventrans for a virtual transfer line (transtype::summedup) which is generated bu inventclosing.

    Anyway thanks in advance for your perfect document and sensitive reply.

    Kind Regards
    Fatih

    1. Are you completely sure that problem is actually in this virtual transfer ?
      Because normally, virtual transfer is not posted into GL, has inventTransPosting.isPosted==NoYes::No, and thus does not produce inventSettlement eligible for GL posting, so I believe Your problem is not in this transfers itself.

      But You can actually patch the class for creation of this transfers. You just need t redefine Dimension() method in InventMov_Vir_Transfer_Closing class. This Dimension will be placed into inventTransPosting.Dimension for virtual transfer.

      Since we transfered out discussion in email,I do not expect an answer in this web-forum’s thread. I decided to post the copy here, just to have a complete copy of our initial discussion on forum.

    2. Hello again Fatih !
      You were right, I was wrong 🙂 It just occurred to me that dummy transfer actually CAN create postable inventSettlement record. It happens on rounding settlement creation for dummy transfer. That is – if on propagating cost adjustment to receipt of dummy transfer, system finds that this is last iteration of closing or that adjustment is below threshold, it ALWAYS creates postable inventSettlement record (with balanceSheetPosting and OffsetPosting specified) even if originating InventTransPosting record has IsPoste=false.
      So – You can either set dimension for the whole pair of issue/receipt records of dummy transfer (by overriding Dimension() method in InventMov_Vir_Transfer_closing class) or You can modify InventCostItemDim.initInventSettlement() to initialize unfilled dimensions to some predefined values if (_initLedger == #initLedgerAlways).
      Actually, I think that in a long term prospect, the second method is better, since it would allow You to use dedicated Purpose dimension value, so You can separate rounding postings from normal receipt revaluations.

      Regards,
      Denis

      1. Hello Again Denis,

        After a while I tried to learn something more in costing in axapta I found your blog too, and see my questions here =) now I have a new question:

        In one of my customers (using ax3.0) I changed the structure of invent posting (just customization without development), and the first testing in inventory closing I realized that some inventory item transactions had been settled, whereever, before I make the changes it did not make these settlements.
        My question is: There are some records in inventory trans table that had not been settled since 2009, and for 2011 6th month closing these records made settlements, but there is also no new transactions since 2009. What may cause this? I looked up inventory trans and real,zed that the valueopen field is yes, and the settle date is empty for these transactions. So it is normal to make settlements, but as i mentioned no new transactions and no settlements since today..
        Thanks in advance

        1. Hello Fatih,
          I understand your situation as the following: You started (or continued) to work on the project, which had not had inventory closing since GoLive or since 2009 at least. When you finally ran IC, you realized that it closed and settled some old transactions from 2009. It is perfectly normal behavior. When IC is being run, it checks ALL records with operation date BEFORE end of closing period. So, it is perfectly normal to modify old records. But this modification goes with the CURRENT date. If you check date in inventSettlement, related to modified records, you will find that this date is the date of inventory closing. You simply have to develop some report, which shows transaction amount for inventTrans not as inventTrans.costAmountPosted+inventTrans.costAmountAdjustment, but as a inventTrans.CostAmountPosted+sum(inventSettlement.costAmountAdjustment; for period until report date). With this approach, it you run this report with reporting date of 01.01.2011, you will see amount without adjustments from recent IC. If you run report with reporting date of 01.07.2001, you’ll see record’s balance inclusive of recent correction.

          I know, that typical accountants objects about record being modified post factum 😉 But you can point them to that fact that adjustment itself belong to current period and association of this adjustment to some old inventory records is a just some internal attribution of the system, which is not to be shown in legislative reporting.

          Regards,
          Denis

  5. Hello Fedetenko

    When we run recalculation for an item, we are getting the following error ,

    “Cannot create a record in Lot level adjustments (InventCostListTrans). Lot ID: 02335332_087, N110T/1.
    The SQL database has issued an error.”

    can you pls help us in this regard.

    1. Hello Anand !
      First question – what version of DAX do you use ?
      Second – try to look into SQL Error log Administration->Inqueries->Database->SQL Statement Trace log. Exact SQL error message would clarify situation a great deal.
      Third – Do you have any customization for IC made ?

      Regards,
      Denis

  6. Hi admin,

    We are using Ax 4.0 sp2 version and please find below the sql error what we are getting while running recalculation for a specified item -N110T/1.

    Object Server 02: The database reported (session 43 (SIS6)): [Microsoft][ODBC SQL Server Driver]Numeric value out of range. The SQL statement was: “INSERT INTO INVENTCOSTLISTTRANS (ITEMID,INVENTTRANSID,INVENTTRANSIDRETURN,VOUCHERPHYSICAL,ADJUSTMENT,VOUCHER,NUMOFITERATION,DATAAREAID,RECVERSION,RECID) VALUES (?,?,?,?,?,?,?,?,?,?)”

    As we are very well aware that Inventory Closing procedure is a critical function in the standard ax application, we never touch upon any customization.

    Kinldy help us to resolve this issue.

    regards
    ana

    1. Hello Ana!

      The only thing,which is clear from SQL error message is that the error is caused by attempt to propagate cost adjustment higher then 9999999999999 currency units.
      I saw something like this on the versions of Axapta PRIOR to version 4.0sp2. In versions 3.0sp5-4.0sp1 Axapta had a serious error which could be triggered by usage of negative inventory together with “include physical value” setting in inventory model. Sometimes, an instant issue cost price went too high (like billions and trillions). In this case, initial inventory transaction was posted, but on attempt to close inventory similar error occurred (IC was summarizing a lot of very high value adjustments, so altogether they were exceeding the Numeric (28,14) value range.)
      This error was fixed in version 4.0sp2, but maybe your installation was upgraded from earlier version ? Please – check: Do you have any unreasonably high costs in your inventory transactions ? (Like several billions of cost price in one inventory transaction).

      If this is the case, try to run inventory recalculation on DAY-BY-DAY basis. Try to recalculate inventory for the period of 01.01.2010-01.01.2010, then 02.01.2010-02.01.2010 and so on. There are good chances that in this case, accumulated adjustment would not exceed numerical range and You will be able to fix the cost prices. After You run all these daily recalculations – try to run normal inventory closing for a month (or whatever accounting period you have).

      If you still have “include physical value” setting checked in your inventory model group – uncheck it. It completely useless in 99% of cases.

      Regards,
      Denis

  7. Hi Denis,

    Thanks for your update.

    For one of our customer, one particular item is causing the issue at the Inventory Closing, This bad item has huge cost figures in Item Transactions. They are facing this issue from the Nov-2009 closing onwards.

    We have escalated the issue to MS also. They have suggested to apply the Hotfix for “Incorrect Cost Figures – KB946804 _40SP1SP2” and we have done the same.

    As you mentioned in your mail, we are running the recalculation on day-day basis only. Somehow we are now able to close the Inventory till december’09.

    We are now in the process of doing recalculations for the month of January,2010. Till 14-jan-2010, we are able to complete recalculation for this bad item.

    When we run recalculation on 15-jan-2010, we are stuck with this SQL error. In the InventTrans table for this period, there are records showing 15 digit values in Value columns (CostAmountPosted, CostAmountPhysical fields).

    We are sure that this High values are causing the issue while running the recalculation. Can you tell us is there any other possibilities to set right the Numeric range for Adjustment column in InventCostListTrans table.

    Kindly help us to solve this issue.

    1. Hi Ana,

      I faced the same problem 6 years ago. I was able to fix it only via usage of non-supported dirty hack. 🙂 I developed a job (full blown class actually) which was running through the list of inventory transactions, setting costAmountPosted to reasonable value (from inventTableModule inventory price), and posting to GL a reversal of difference between old and new cost price. I cannot recreate this class now (and I have no ways to test it), but I think that since in Your case You have a limited number of invalid inventory transactions, You can simply modify costAmountPosted manually (via table browser or SQL Management Studio) and then create and post GL journal for the difference between old and new cost prices. Do not forget three rules:
      If you modify issue transaction of transfer (transfer journal, quarantine journal, WMS transfer journal, transfer order), You MUST also set the corresponding receipt transaction to the same costAmountPosted.
      If you modify issue transaction of production (PO or BOM), you MUST adjust receipt transaction on the difference between old and new costAmountPosted in issue transaction.
      You should only create and post GL journal if InventTransPosted record for your inventory transaction is set to Yes.

      Another approach (but I have never tried it) is to modify REAL definition in SQLSystemVariables table to something like Numeric(38,14). You should modify this table (It can be done ONLY via SQL Management Studio), sync database, close inventory and then MODIFY this value back and sync database again.

      Both ways are very dangerous, so try them SEVERAL times on test environment.
      I should mention also that both approaches also are completely unsupported by Microsoft, so You are on your own…

      Regards,
      Denis
      P.S. If you will try the second way – please inform me on results 🙂 I never tried it, but if it work – it would be great !

  8. Hi
    I would like you to give me a more crisper scenario for the Negative inventory without marking the Physical Negative Inventory option. We are currently a similar issue, wherein the inventory is going to negative inspite of the Physical Negative Inventory option has been unmarked in the inventory model group.

    1. Hi Vamsi !

      Scenario is pretty simple:
      1. Create new inventory item (just to be suer that we do not have any history on this item). Set its inventory model to any model with both negative Physical and Financial inventory flags unchecked.
      2. Create new purchase order for 100 PCS of the item. Post purchase invoice with today’s date (15 May 2010).
      3. Create sales order for 110 PCS. Post sales invoice for 70 PCS with yesterday’s date (14 May 2010). Invoice will be posted without any error messages or warnings.
      4. Post another sales invoice for 30 PCS with the date of 1 May 2010. Invoice will be again posted without any error messages or warnings.
      5. Now post another sales invoice for 10 PCS with ANY date (say – tomorrow – 16 May 2010). This time, attempt to post invoice will fail with error message complaining about negative inventory.

      It is pretty obvious that first and second sales invoice was posted with the dates BEFORE actual arrival of the item into inventory.

      Regards,
      Denis

  9. Hi Densifed,

    I have tried out this scenario and you were right. But my concern is that, we have a client for whom the inventory itself is going to negative and inspite of clearing the physical negative inventory option. Strange thing is that they are able to sell the items, even if the Physical Inventory is negative. This has been haunting the client for quite sometime and they have no real clue what exactly the stock is. They are using an add-on named Inventory II which has got its own internal costing engine which would maintain the per batch cost using the receipt lot id. We have asked the VAR to give us a clue and they say that the latest patch would fix it. We have tested it on the test environment but does not seem to fix it. Any suggestions?

    1. Hi Vamsi !

      Actually, all this ‘negative inventory check’ functionality checks only CURRENT inventory level. So – If your customer has item in stock NOW, they can post sales invoice with ANY date from the past (like with date from 1940s or just 1 May of current year). Somewhere in my article, I wrote that MS (probably) has implemented inventory check this way just to prevent delays on inventory update process. It is just designed this way, you can not fix it without implementing customization.

      I heard about Inventory II module, but I never had any experience in working with it.

      Regards,
      Denis

  10. To be more specific Denis, is there any real chance for the ‘Physical Inventory’ field in the InventSum table rather the Invent On-Hand form, go negative without marking the Physical Negative Inventory with these kind of transactions?

    1. Nope. It should not be this way. If ‘Negative XXX Inventory” is turned off, then physical/financial inventory should not became negative in invent on-hand data.
      Two possibilities came to my mind:
      1. You are looking at inventory on-hand data with incorrect subset of inventory dimensions. Say, You have site+inventory location as physical inventory dimensions, but You try to look at inventory on-hand for site+batch id combination. Physical inventory level can be checked only for subset of physical inventory dimensions, Financial inventory level can be checked only for subset of financial inventory dimensions.
      2. I saw inventory on-hand going negative as a result of incorrect addition of extra inventory dimensions. (Especially – if new field has not been added to inventSumDeltaDim table). Please, check whatever new inventory dimensions has been added.

      Regards,
      Denis

  11. Thanks a lot denis. I have cross verified and found that the new inventory dimensions have not been added into the InventSumDeltaDim Table.

    1. You are welcome ! Actually – if You are adding inventory dimensions, You need to check (and modify as needed) all places in X++ source code which are marked with InventDimDevelop macros. The macros itself is empty, but it is used by logistic module developers in MS to mark the places related to inventory dimensions extension.
      Regards
      Denis

  12. Hi Denis,

    I’m back to trouble you again :D! Well I now have a strange requirement. Would we be able to track the cost an item per batch? Let’s say i have an item I with batches B1 and B2. B1 has a cost say, 15 and B2 has 16. When I sell,transfer or issue the items, AX would take the average cost of the item i.e., 15.5. Can we maintain the batch cost through out the system during issues, sale or transfers? If yes how, if no why?

    1. Hi Vamsi !
      Simply include batch into financial inventory dimensions in inventory dimensions setup.
      Actually – I have written about this in “Per-Batch costing” and “An inventory lot as a basic unit of cost” sections.
      Denis

  13. Aight Denis! Would go through it! But I have been trying to figure out how to maintain batch cost, during transfer orders. No matter how many times i juggle with the dimensions, I find the same average cost being transferred!

    1. Sorry Vamsi, but this problem is totally unsolvable in standard Axapta. Only VERY heavy customization can do the trick.

  14. Yea! This is what Inventory II is supposed to do, but does not do it in a fulfilling way either!

    1. I have been thinking about possibility of such a customization. Here is the short list of things to implement:
      1. Split normal inventory transfer journal functionality into two different journals. One would only support transfers inside the same financial inventory dims, another one – would support merging/splitting financial inventory dimensions.
      2. Change running average calculation algorithm for instant issue cost price calculation to take into account respective financial inventory dimension values (i.e. calculate costs on lot+financial inventory dimensions principle, not just ‘only lot’ principle).
      3. Change inventory closing functionality to track costs on lot+financial inventory dimension principle.
      All this seems to be feasible, but I would ask for at least 1 month of work to develop and test this customization. (It is not a quote, I am actually fully utilized for the next 5-6 months:))

      Denis

  15. Hi Denis,

    We have another requirement for our client regarding reservations and picking strategy based on Expiry Dates. Do you have any idea of an add-on which would work on these lines? What do you think about customizing the whole thing in AX?

    1. Hello again !

      Well – there is nothing too complex about this customization. You need just modify methods inventUpd_Picked.updatePickMore(Slightly) for picking and InventUpd_Reserved.updateReserveMore(Well – more than slightly:)) for reservation. The idea is – You should add inventBatch table into loops other inventTrans and add order by inventBatch.expDate. It is very yeasy for picking, but for reservation You need to rewrite all inventDim selection logic. (Which normally allows You to automatically fill-in unspecified inventory dimensions during reservation).
      The only drawback of this customization that I could imagin, is slightly increased chances for deadlocks during simoultaneous updates to the same inventory lot. Say, If one user is adding reserve to given sales line, while other user is posting sales invoice for the same line, they will use different order of updates for the same inventory lot. (Same set of inventTrans records). This will definately lead to deadlock. From other side, normally Axapta simply restarts and redos transaction in case of deadlock, so most probably user won’t notice anything, just invoice posting will make more time than usual sometimes. Also, It is not very typicall case, when two users are working on the same inventory lot at the same time. So – I would dare to implement this.

      Denis

  16. And I also have a doubt regarding the Reference Lot ID.When does this field will hold a value? For most of the transactions it is left as blank and I have only for a few transactions it is filled with some value.

    1. Hi Vamsi !
      Reference lot ID typically holds value when:
      1. User just marks inventory transactions directly, via inventory->Mark inventory button.
      2. When user firm planned orders in “Update marking:Standard” mode.
      To be honest, I do not have English version of Axapta handy right now, so maybe I misspelled names of the buttons a little:)

      Denis

  17. Awesome article!! I haven’t found any article with such detailed information about DAX inventory costing and closing. Details of changes in multiple versions makes it even better. Good job, keep it up.

  18. Hi Denis,

    I have checked the table InventTransPosting. It has a field called InventTransPostingType which is an enum and has got three options namely Physical,Financial and Physical Revenue. Could you explain us what does this mean? For a Transfer Order, i find the InventTransPostingTYpe as Financial, where as when i use that voucher and check in that account i don’t find the financial posting for that particular transfer order. Can you throw some light on these three options?

    1. Hi Vamsi !

      Normally, every logistic document and,correspondingly, inventory transaction, pass through two stages of posting Physical and Financial. Please, remember, that word posting can be used in a wide sense, like “acceptance” or “registration”, so usage of word ‘posting’ does not automatically imply creation of any GL transactions.
      Physical inventory posting is occurring when document is accepted by logistical authorities (like acceptance of vendor packing slip into warehouse or acceptance of report-as-finished journal from workshop to workhouse). Financial inventory posting is a next stage and it is occurring when corresponding document is accepted and processed by accountancy.
      Now about posting to GL. Usually, financial inventory posting creates GL postings. (Say – during posting of vendor/customer invoices, production costing, inventory journal posting and so on). But, some of the inventory operations, namely various kinds of transfers (transfer order, transfer journal, quarantine journal, WMS journals) do not create GL postings during financial inventory posting. Again, usually physical inventory posting does not create any GL postings. But user can setup creation of GL postings during physical inventory postings (inventory model->Post Physical Inventory). Later, during financial posting, this preliminary physical transactions are reversed. This mechanism primarily is used for two purposes: Posting of accruals for delayed invoices (when we received some goods from vendor, but invoice stuck somewhere en-route) and as a mechanism for packing slip< ->invoices reconciliation (if all packing slips have corresponding invoices posted, then physical posting accounts must have a zero balance for given date). If some operation (again – transfers is a good example) does not creates financial GL postings, then physical GL postings are also are not occurring for this operation.
      InventTransPosting table is allways being filled during inventory physical/financial update. The most interesting field regarding your case is the IsPosted field. If this field is set to True, then some GL postings actually were created during the operation. If field is set to False, then there were no postings during the operation and this record was created with some dummy values for GL accounts.

      Physical Revenue mechanism was created later ( in Axapta 2.5 Option Pack as far as I remember). Normally, physical posting creates only two GL postings. It is enough if you want to create accrual for issue/receipt to/from inventory. But if you also want to create accrual for revenue part of invoice posting, then you need to create TWO pair of postings (one for accrual of D COGS C Inventory, another for accrual of D Customer C Sales records). So, they implemented the additional mechanism of physical revenue posting, which again can be activated from Inventory Model Group setup. Since in this case the system need to reverse TWO physical GL transactions during financial posting, designers or Axapta decided to create additional record for Physical Revenue posting in InventTransPosting.

      I personally think that all this Physical Revenue posting is an obvious design flow. First of all, according to IAS/GAAP standards, only COSTS can be accrued. Revenues should be recognized with diligence, so not Revenue posting allowed (even preliminary) before actual invoice issuing. Second, from consistency of the system’s design point of view, this Physycal Revenue Posting records look like design wart

      Regards,
      Denis

  19. Hi…!

    Can I add the physically updates purchases (purchase order with packing slip update) in the inventory closing not only financially update in other words… the period wegited average will consider fianancialy and physically updated quantities for issuances and inventory closing afterwards.

    Regards

    1. Hi Mohamed !
      I do not think that this is possible (even by employing the complex customization). The reasoning behind this conclusion is the following:
      Actually, when physical transactions are being processed during inventory closing (say for FIFO/LIFO models), they aren’t being properly closed. It looks more like inventory recalculation – if some transaction is settled to physical one, then no settlement record is made, settled quantity is not being increased;Only cost adjustment for issue transaction is being made, like during inventory recalculation.
      Now let’s think about what would happen if we were to hack inventory closing a little bit to enable physical transactions processing during inventory closing. Due to very nature of average costing, every receipt transaction is contributing to every issue transaction. Say, during closing we have financially updated receipt transactions for total qty of 60 pieces, and physically updated transactions for total qty of 10 pieces. Let’s also assume that we have issues for all 70 pieces. Then during inventory closing, issue transactions would be settled only for 60 pieces, since this settlement is proportionally spread over every issue transaction, thus every issue transaction would be left open (since it would not be fully settled) and on the next inventory closing, EVERY issue transaction from previous period would be re-valuated again. Very soon we would face the situation when EVERY (or almost every ) issue transaction since the system start is being re-valuated during inventory closing (Because they would never been closed totally, since every open inventory transaction during every inventory closing would be remain unsettled for some part, which was settled to physically updated receipt). This would lead to catastrophic performance decrease for inventory closing.
      Actually – I think that You had better look for some other solution for your business issue. I guess – You actually are trying to implement some kind of accrual costing. Say – Your customer does not have vendor invoice, but since the customer is 99% sure about the cost, he/she wants this purchase invoice to participate in costing process. Once I implemented the following customization to deal with similar customer requirement:
      1. I changed packing slip posting class (salesFormLetter_packingSlip.updateNow()) to create FINANCIAL updates instead of physical. This financial updates was posted from some special intermediate ledger account to normal Purchase receipt ledger account. The cost price in inventory receipt was calculated from this preliminary purchase value in purchase order lines.
      2. I Changed invoice posting class, to create GL postings to the special intermediate account instead of making any inventory updates. This transaction was posted with a final vendor invoice value (from purchase order lines, now updated with actual price from vendor invoice).
      3. In the end of period, the balance of this special intermediate account (with total value of variation between actual and estimated prices) was allocated to COGS account proportionally.

      Regards,
      Denis

  20. Thank you Denis for your feedback
    You’re totally true the case is vendor invoices are delayed and company need to recognize the inventory cost (invoices + packing) in the closing since they know the actual cost (99%) ….

    When the closing exclude physical receipts (packing) that does not gives accurate average cost as book says…!!!!

    In the other hand I have another question does AX identify quantities received physically, and identify quantities received financially and physically if yes (that what I guess) when I issue physically update quantities, during closing AX could not settle that quantity, then when receive the invoice in the next month and closing the next month AX creates adjustment entry and mark it to the previous month transaction (quantities could not settled) from business point of view it’s in accurate since there will be changes from month to month and from year to year…. and auditors will not like that…. correct me if I am wrong 🙂

    Regards

    1. Hello Mohamed !

      It took three days to think about answer to your question:)
      First of all I never heard about any IAS/GAAP standard which would define exact details on cost price calculation. So, “Accurate average cost” depend on your definition of accurate cost, not on some generally accepted standard. Furthermore, as far as I remember, GAAP insists on early recognition of liabilities and expenses; Assets and revenue must be recognized only after voucher has been received. I think that formally, your customer’s request to include cost price from packing slip into average cost calculation, dos not comply with formal standard. I must admit though, that I’ve meet similar requirements from several customers. I think that the best solution is to book the item’s receipt into inventory with estimated cost price, then, on arrival of proper invoice, to post the difference between estimated cost price and actual cost price into some of the current expense accounts. I described how to implement this approach in my previous message. If You do not want to implement customizations to Axapta, you can simply book non-invoiced items via inventory journal, and then, when the invoice arrives, you can post it to GL account via vendor invoice journal. It is not the ideal solution (since both inventory receipt and AP record was created independently from purchase order), but it does not require any customizations at all…

      Now, speaking about your question about late adjustments to inventory data. Maybe You are not completely aware about it, but currently Axapta DO support late corrections to iventory. Say, You can purchase some merchandise and sell it in September. During inventory closing, both receipt and issue transactions have been closed. Then, say, today, we receive delayed invoice from transportation company, with charges for transportation of given merchandise in September. We can create and post in October markups for invoice posted in September. In this case, related receipt inventory transaction will have transaction date from September, but one of the related records in inventSettlement will have date from October. Then, during October’s inventory closing, issue inventory transactions which was settled to given receipt will be adjusted to the new issue cost price, with transportations costs included. I should emphasize, that all adjustment records will be made in October, both in inventSettlement and General Ledger. So, I think that this would not concern auditors, since all adjustment was made in open period (October), even if some of these adjustment was made for the records from earlier periods.
      So, I guess, if we forget about performance problem for a moment, we can theoretically implement the costing scheme which would support true inclusion of physically updated transactions into average costing. But if we remember about possible performance issues and the number of adjustment which would be made to every transaction if we were to implement this scheme, it would be clear that it is not solution at all.

      Regards,
      Denis

  21. Hi Denis,

    Could you throw some light on the parameter “Automatic Addition” in the inventory parameters? What is the purpose of marking that check box? Any test scenarios where we can test it?

    1. Hi Vamsi !
      It is very simple. When this parameter is turned on, the system tries to merge several inventory transactions with the same lot id (inventTransId) into the minimum possible number ones. You can try the following scenario:
      1. Turn this checkbox on
      2. Create a sales order line for 5 pieces.
      3. Open the inventory transactions form, split this transaction onto,say, 3 separate inventory transactions.
      4. Return to the sales line. Change quantity to 6 pieces (actually – we simply need to change something which would cause the system to update underlying inventTrans)
      5. Open the inventory transactions form. Now You see only one inventory transaction line again.

      Regards,
      Denis

    1. Just to compress the table and keep it of the lowest possible size.
      BTW – I almost allways set this checkbox on my projects…

    2. I would add, that you can have a lot of similar unmerged inventory transactions not only because You splat them manually for purpose. If you reseve inventory lot from mutliple batches and then unreserve it, or you pick lot from multiple batches and then unpick it, you will also end up with a lot of similar records, which can be easily merged into one.

      1. This is a great post – made even greater with use of the word ‘splat’ when referring to inventory transactions. I’m staring at an inventory close now with the InventTrans table is most definitely splatted.

        1. Thanks Dan!
          Probably I need to clean up the rust from my irregular verbs. Somehow I was thinking that I should use saxon past, like split-splat-splatten or split-splat-splut 🙂 I will clean up my article later (since it take time to load and unloadit from site).
          Regards,
          Denis

  22. Hi Denis,

    I am a keen reader of your blog. I am working on Microsoft Dynamics AX 2009.

    I am facing an error while transferring a standard cost item into fixed asset. Error is “Function InventCostTrans.setRefTypeFromInventTransType has been used incorrectly.” I am getting this error only while transferring a std cost item into fixed asset. I am able to transfer weighted avg./FIFO items into fixed asset easily.

    Can you please throw some light into this…

    Thanks in advance…

    1. Hello Aman, Looking into the code of the function, you mentioned, I found pretty interesting comment:
      // Not including of the following types:
      // SummedUp … Used for summary transactions created in inventory closing (weigthed average)
      // Asset … No standard cost transactions with fixed assests

      Actually, I think the author of the code and designer of new standard cost simply did not expect to inventory item to be moved into FA directly. Normally, when you are installing/assemblying FA, you should do it via project module. Next, when FA is finally assembled, you can move project’s delivered item to a fixed asset. Project items (I mean – item delivered as a result of project – I do not remember how it is called in manuals), normally (according to various GAAP/IAS standards) should never have standard cost price. (Since FA, by definition, is unique product, which simply can not have standard cost).
      Of course, for simple Fas (Like, say, notebooks in standard configuration) it is not very convenient, but it is the only true way. ;-). If you want a more lightweight mechanism, You can create two items: One (Notebook Acer:SC) with standard cost model, another (Notebook Acer:FA) as a BOM with FIFO model. Next you can create BOM journal which will write-off one Acer:SC into intermediate GL account, and assembly one Acer:FA from the intermediate GL account.
      Both approaches (with project module and BOM journal) are also more correct from accounting principles point of view, because normally, materials can not be transfered from Inventory to FA account, there always should be intermediate account…

      Regards,
      Denis

  23. Hi Denis,

    Firstly, wishing you a very happy new year! Hope you’d continue to serve the AX community with your useful posts about Inventory. Well I have ran into a kind of situation where I would need your suggestion/help. Why does AX does not consider the dimensions mentioned on Item Arrival Journal to show up in the ‘Ordered’ status? Say for eg., we have 5 items that need to be received, 3 with batch b1 and 2 with b2 originating from the same purchase order. When i go to the item arrival journal and create 2 different lines for 3 and 2 and assign the batches b1 and b2, the system has to show the quantities for 3 and 2 with different batches in b1 and b2. Is this the standard functionality of AX that the batches or rather the dimensions would not be shown on the ordered status? Else is there something that needs to be setup? If you can guide us in how to achieve this, we would be solving one of the most important issues for our client.

    1. Sorry Vamsi. I have no idea why it is made this way. Neither I have Axapta 4.0 to have a look.

  24. Hi Denis,

    We have a client who wants to change their inventory valuation method form Weighted Average to FIFO. Would you happen to know how to go about this change and the possible implications it may have?

    thanks…..

    1. Hi Emmanuel,
      It is more like methodological question. In version 2009 you can change costing method to FIFO and remaining (not closed) part dummy of receipt from virtual transfer will became very first FIFO receipt. In versions 4 and 3,situation is a little bit complicated, because if you just change costing method from Average to FIFO, essentialy EVERY not fully closed receipt will be a part of FIFO closing in the next month. Since with old Weighted Average method, number of this partly closed receipts can be very high (like receipts from last 4-5-6 months – depending from your setup), results of first FIFO closing will be hard to describe and justify to accountants/auditors. So if You use versions 3 or 4, it is better to write-off all remaining inventory on the last date of the Weighted Average closing period and then receive it with appropriate cost price on the very first day of FIFO period. In case of DAX2009 this is not necessary, You can just change costing method.

      Reards,
      Denis

  25. Hi Denis,

    Thank you very much for the information….

    More power to you!

    Emmanuel

  26. Hi Denis,
    How about changing the inventory valuation method from Standard cost to FIFO…? IS this possible? I have tried to zero out the inventory , but when I change the valuation method an infolog appears….“The item is set to be evaluated by inventory model ‘Standard cost’. The system cannot convert the item to be evaluated by an inventory model of type ‘Transactions settled’ only inventory models of type ‘Transactions not settled’ can be selected.” What can we do to effect the change?

    Thanks,
    Emmanuel

    1. Hi Emmanuel,
      I am afraid it is not possible (even with the great deal of customizations). I think, the best solution is to rename old items from, say ‘Nut’ to something like ‘Nut-Std Cost’, then create new inventory item ‘Nut’ and then write-off old inventory item and receive new one via inventory journal. The new standard cost mechanism is totally different from other costing methods. For new standard costing there is NO Settlements between receipts and issue. If you will try to disable the check, which produces this error message, next inventory closing will totally mess up, because it, probably, will try to settle old receipts, made with old costing approach, to new issues and results of this can be scary 🙂

      Regards,
      Denis

    2. Hi Denis,

      Thank you so much for the inputs……

      All the best…

      Emmanuel

  27. Dear Sir
    Many thanks for your extended knowledge in above article.
    I facing problem as follows:
    Inventory valuation method is weighted average. Customer is a wholesale and does not have any BOM or service items.
    Every time I tried to close inventory I have this error “ Transaction Sales order No…. Lot ID …. “unit cost price can be wrong as the transaction cannot be fully settled”
    I extended the Maximum throughputs up to 250, but the minimum throughputs adjustment is 1.
    When trying to get inventory detailed value through report Reports>> Status>> Inventory Value>>inventory value by inventory dimension.
    I getting totally wrong numbers and the difference is huge. Why this is happening?
    Nasser

    1. Hello Nasser,

      First of all: What version and service pack of Dynamics AX do you use ? Handling of average has changed a lot in version 2009 and I also heard that for some markets, Microsoft backported the new scheme to version 4.0 SP2 (or some of post-SP2 fixes).
      Second: The message that you see, simply tells you that you do not have enough inventory receipts, which would cover your inventory issues. In the end of inventory closing, the system search for all unclosed (having settleQty

  28. We have recently implemented AX 2009. We have a problem that we are not able to resolve. The inventory produced is issued at a higher price than the actual produced costs.

    Any suggestions would be appreciated.

    1. Hello Andre,

      Please, check cost prices of RECEIPTS from production orders (or BOM journals if you use them instead of Production orders). If receipt cost price is Okey, then we have some more or less simple issue of inventory closing. If the root cause of issue is receipt cost price of finish goods released from production, then situation is more complex.
      Please, tell me what costing model do you use, what are the financial inventory dimensions for finish products?
      If the production release receipts also have incorrect cost price, please tell me:
      1. Do you use any vertical solution for process industry ? (Since your business seems to be process-based by nature).
      2. Do you use any indirect costs in costing sheets ?
      3. Please, find one production order with incorrect receipt cost price, and calculate total amount of cost price from inventory issues into production orders (from picking lists) and total cost price of labor and indirect expenses (from production journals). Maybe, actually cost prices in journals and inventory transactions are correct, but they are incorrectly posted to GL and you have negative balance in GL.
      4. If you will find production order for which the total cost price of released inventory receipts is not equal to total cost price of consumed materials+ total amount of labor and indirect expenses, then we are facing some kind of bug (in standard Microsoft’s code or in customization, or maybe someone has modified data directly)
      5. Does inventory closing influence cost price of production receipts somehow ? Are adjustments actually calculated for production orders in question, when you close or recalculate inventory ?

      Regards,
      Denis

  29. Wonderful job. Thanks for sharing your knowledge on closing. It helps a lot. And you are execellent at interpretation.

  30. Hi Denis,

    Thank you for your in-depth review on AX inventory.
    Denis, is it possible for a batch quantity of an item which has been financially closed, still have “residual value”?
    I’m experiencing at my customer’s database (AX 2009 SP1), somehow on the on-hand form and report, there are item batches for which the quantity is already zero and financially closed, but value-wise still has residuals e.g.: $ 0.01 or -$ 0.01. How to rectify this?
    (my customer is using FIFO, Fixed Receipt Price. We also modified the item’s unit price to become 5 decimals in the PriceCur EDT)

    Thanks and regards,

    Alphonsus

    1. Hello Alphonsus
      First of all, check for inventTrans records, belonging to specific batch, which does not marked as closed. (ValueOpen==NoYes::yes). If this records exists – try to check settlement history for them. (For me it looks like some customization has damaged standard mechanism for writing-off errors and roundings). If all inventTrans records are marked as closed, than for some reason you have incorrect inventSum (invent on-hand) data. It can be easily recalculated via inventory module data check in Basic->Periodic->Check.

      Reards,
      Denis

  31. Hello Denis- Thank you very much for the detailed analysis

    I have a client who is running on 4.0sp2 and has not closed Inventory for four years and they are trying to close Inventory now. They have changed their costing versions couple of times and are on standard cost (cost updated 2-3 times) now. What are the pre-closing steps that needs to be performed to take care of the change in costing version and changes in standard cost? Plan is close inventory monthly from month 1.

    Thanks
    Sri

  32. Hi Denis,

    Thank you for an interesting article. Helps me in understanding the inventory closing process much better.

    We are faced with a particular issue and was hoping if you can help. Our client is running AX2009 SP1 and using the Weighted Average Date model as its costing method. After running our inventory close, a particular item has zero quantity but a negative cost amount. How do I correct this? There is no stock on hand for the period we run the stock close. I also tried marking but the negative doesn’t go away. Please help!

    We are costing by location and also using site and warehouse, all have financial inventory switched on.

    Kind Regards,
    Anisha

    1. Hi Anisha

      There are two probable reasons for your situation. First of all, It might be that your on-hand data (inventSum) is damaged. Maybe you have perfect data in inventory transactions, but inventSum is went unsynched from inventTrans. In this case, you can recalculate inventSum by running Module data consistency check for Inventory Module and onHand data. (If you do not want to waste time on recalculating every item, ask one of your developers to look into InventSumRecalcItem class and write a job which would call this class for your damaged item).

      Another possibility (which is less easy to fix) is that somehow, you have combination of the following negative issues:
      1. Some of your inventory issues for the item in question are not closed. Say, you have zero qty on hand for now, but in reality – your issue was made 2 months before your first receipt. In this case, issue will remain unsettled and will have unadjusted issue cost price.
      2. Some of your inventory issues for the item in question has abnormaly high instant cost price. (I do not know why. Maybe they was posted with no inventory on-hand, and system used default cost price from inventTable module. And this price was too high).
      In this case – you inventory value is negative, because of these issues with abnormally high instant cost price;Inventory closing does not fix this situation, because it ignores these issues (as they do not have suitable receipts, to be cost-adjusted from). In this case:
      1. Try to find inventory issues, which was issued during the period, but still have Open flag set after inventory closing.
      2. Try to find and post missed inventory receipts for these items. (Maybe you will need to find original vouchers from accounting archive).

      I think, second case scenario is much more probable…

      Regards,
      Denis

  33. Hi there,
    im running ax4.0, no sp’s applied
    when i try to run adjustment of on-hand inventory , there is an error msg appering that prevents further update – ‘negative cost amount for receipts is not allowed’.
    why is this happening?
    i noticed an interesting issue , when i go through transactions for that specific item, i can see a lot of them where cost amount column is empty.
    the value should be costAmountPosted+ costAmountAdjusted., so why is it empty? actually, why is costammountAdjusted field in inventTrans zeroing out costAmountPosted , when thansaction is created?
    does this have to do something with adjustment error?
    thanks for your help

    1. Hello Pedja

      First of all – do not be surprised that on-hand adjustment is modifying costAmountAdjustment in inventory transactions. In Axapta, on-hand adjustment is just a convenient mechanism which first calculates total difference between current and adjusted on-hand values and then spread the difference between non-closed transactions, proportionally to the factor, you specify in adjustment form (Adjustment->Cost Price, Adjustment->Percent and so on). The error message, you mentioned tells that you are trying to adjust cost price for inventory receipt (with positive quantity) below zero.
      The most probable reason for it is that your on-hand data (inventSum) went out of sync with inventory transactions data (it happens sometimes). Say, for some item you have two receipt inventory transactions 5 pieces for 200 USD and 7 pieces for 350 USD. Because of damaged inventSum data, total amount on hand is,say, 1300 USD (instead of 550USD). You are trying to fix the issue, by applying on-hand adjustment of 550-1300=-750 USD. The system tries to re-valuate first transaction by 200/550*(-750)=-272,73USD and second transaction by -750-(-272.73)=-477.27USD. Since after applying these adjustment to transactions, they would have positive quantity and negative cost price, the system pop-ups an error message.
      If my assumption is right and you simply have damaged invent on-hand data (inventSum), instead of running on-hand adjustment, you need to run module data check (Basic->Periodic->Consistency Check) for Inventory Module. Select Fix (not Check) and check the checkbox for Inventory Management->Item->On Hand. Un-check all other checkboxes (they are lame and irrelevant to the issue). Take a mention that this procedure iterates over all items in Inventory Details (inventTable) and during recalculation of an item, all user operations on this item can be blocked for 5-7-10 minutes (depending on the number of inventory transactions for the item). So, it is better to run this job during time of lowest user activity. Also, it is worth to test it on testing environment before attempting the same thing on production environment.

      Regards
      Denis

  34. Hi Denis,

    Can I ask your opinion on AX 2009 inventory costing? If we choose to use FIFO costing (with Fixed Receipt ticked), does it mean that whenever there is an item price change, we must always adjust on-hand quantity to this new item price, with no exception?

    Thank you,
    Alphonsus

    1. Hi Alphonsus
      Yes – It looks like it. I think, the whole ‘fixed receipt cost’ mode was invented as a poor man version of IAS/GAAP standard costing. Every receipt from outside of a company is automatically re-valuated to some predefined cost price. If you want to change this predefined cost price, it is quite logical (not from technical, but from financial point of view) to evaluate your stock on hand to the new price. If you would not do it, Axapta still can handle it, but after you changed the cost price, first issues will go by old cost price (according to FIFO), then next issues will go with new cost price. Instant cost price will be changed to the new value immediately, but IC or inventory recalculation will still bring the situation to old-price-first inventory issue costing approach. If it is Okey with your financial dept – then you can skip inventory revaluation on cost price change…

      Regards
      Denis

      1. Hi Denis,

        I acknowledged your explanation. However, to your knowledge and experience from AX/system perspective, are there, by any chance, adverse impacts of not revaluing inventory cost accordingly? (assuming that my Financial Dept agrees not to revalue them from business and regulatory point of view)

        Thank you.

        1. Hi Alphonsus
          Yes – There is no any adverse effect for enabling ‘fixed receipt cost’ without revaluating inventory. It is totally Okey from ystem’s point of view.

          Regards
          Denis

  35. Hi Denis,

    Thanks for useful and informative article.
    We ran the inventory closing in ax 2009, but after that all the stock in warehouses are disappeared, it is showing in the sites but not in the warehouses.
    Kindly advice what it cased and how I can fix it.
    Thanks,

    1. Hi Khalid
      I have never heard about such a strange results of inventory closing. What do you mean by ‘all the stock in warehouses are disappeared’. Does it mean that now you have zero QUANTITY for item+warehouse report? Or does it mean that you have strange Monetary balance for item+warehouse report? If you have quantity problems – well it is really weird. I can suspect a bug in weighted average method, which creates this dummy transfers with wrong quantity, but I never have seen something like this before. If you mean that you just have a strange monetary balance for item+warehouse, please remember that you can check the monetary balance ONLY for inventory dimensions, marked as ‘Financial inventory dimension’. If you have marked only Site as financial inventory dimension, then on attempt to check on-hand monetary balance for item+warehouse you can have rather strange results…

      Regards
      Denis

  36. Hi Denis,

    Thanks for your reply,

    Yes, we have Zero Quantity for items in most of our warehouses after Inventory closing. While extracting the inventory by inventory dimension aging report as on closing date it is showing Zero Quantity but when I am extracting the same report as on one day before the Inventory closing it is showing me the quantity.

    1. Well, then you probably have some issues with this phony transfer inventory transactions. Try to cancel inventory closing and close it again. I hope it will help 😉
      Also, check that you use recent Rollup for DAX2009. Early versions of DAX2009 did have issues with this phony transfer (although I never heard about something this serious). Somewhere in RU3 or RU4 these issues were fixed.
      Also – how did your last closing go ? Did your AOS have any crashes during closing ?

      Regards
      Denis

  37. Today I come with questions 

    1.What impacts are having Inventory closing and recalculation process?

    2.What if I run the recalculation only? Not the inventory closing.

    3.If I run the recalculation, will I be able to do the adjustment?

    Many Thanks for your time. 

    1. 1. Well – the whole article is about that question. In short:closing revaluates inventory issues and receipts to true cost price according to costing model. Also,after inventory closing, the period is blocked for any logistics related operation.
      2. Recalculation does not save issue-to-receipt settlement information; recalculation does not write-off roundings and errors (so after recalc you can have item with zero quantity, but with some small monetary remain); recalculation does not block closed period for further logistic operations.
      3. You will be able to do adustments. Yet, usage of recalculation instead of closing is not very god thing in a long run. In 2-3 years you will have a lot of records with hanging monetary remain (because roundings are not written off on reclauclation); Also, since you do not block logistics operation in closed period, it highly increase chances for users to erroneously post operations in closed period…

      Regards
      Denis

  38. Hi Denis,

    I have 2 questions regarding direct delivery
    1. I have faced a strange error while trying to register an item in a direct delivery case ( I had to register them since the item has got serial number marked and there are more than 1 quantities required). I got something like, the “order status is reserved order with xxxx sales order, remove the marking and then post (not exactly these lines but something similar to this.). Later I found out that this problem is occuring due to marking the financial inventory on the dimension group for serial number. i have unmarked it and it worked. Is it the reason because that the item has got a marking, the system does not allow the financial inventory marked for an item to process thru a direct delivery?
    2) Does inventory closing need to be performed in case of direct deliveries? To be more precise what happens if an item is invoiced fro sales initially and then purchase invoice has been posted for a different cost in case of direct delivery? I have a scenario where the goods are brought into the inventory in case of only returns. THe sale is thru direct delivery, but the returns might have the goods to be returned to a logical warehouse to be returned to the vendor (as there is no direct delivery in case of returns in AX as far as i know). Does the inventory closing needs to be done in such cases as well? Is there a way to avoid inventory closing in such cases? Any help regarding these would be appreciated.

    1. Hi Vamsi

      1. Hard to say exactly. When you use direct delivery, your sales line do got marked against purchase line. But I can not imagine how it can influence registration. The message you mentioned can happen when you decrease quantity in purchase order which is marked to salesOrder with ‘reserve ordered’ lines.
      2. Inventory closing is always a good thing to do. First of all, if you work with negative inventory, your instant issue cost price can be pretty strange (either zero, either value from invent item definition). Second, as you mentioned, you have to use standard returns. Essentially, there is nothing special abotu direct delivery handling in axapta. It is a just marking+some simple mechanism for synchronizing quantities and dates between related salesLine and purchaseLine. For inventory closing, direct delivery is a just a pair of marked inventory transactions. There is no other special treatment of direct delivery in IC.

      Regards
      Denis

      1. Hi Denis,
        I have got the issue. I presume that this is because marking the ‘Primary Stocking’ checkbox. I have unmarked the ‘Primary Stocking’ checkbox and left the financial inventory marked and performed the registration and I did not face the error. By your suggestion if Inventory Closing is performed when there is returns to the W.H., how would it affect the costing of the item? I mean it is serial number controlled item and also each serial number will have different costs and if the costing method of the item is FIFO, will it take running average for all the serial numbers and while closing take the FIFO cost?

        1. Hi Vamsi
          Yes – it will. But there is one important point: When you are posting customer returns you MUST fill-out ‘Returned lot id’ in salesLine and you must return every serial in separate salesLine. If you fail 1st condition, your inventory receipts won’t be revaluated from respective inventory issues costs (COGS). If you fail on 2nd condition, your different cost price from several serials will be merged in one inventory lot. In this case – all future inventory transactions with these merged serials will have the same cost price. (Cost is calculated per inventory lot, as I wrote somewhere in my article. If you post several serials with different initial costs in ONE inventory lot, their costs are merged…)

          Regards
          Denis

  39. We are having an issue where the creation of counting journals for our stock items is getting slower and slower each month. Some inventory warehouses are experiencing wait times of over 3 hours. We have been with AX for over 3 years now. Is there any kind of clean-up or process that we should do to speed up the creations of the counting journals?

    1. Hello Mike
      Inventory Counting journal creation is based on reading of inventDim/inventSum tables. Sometimes, size of inventSum can be slightly decreased (like several percents), by running module consistency check in Fix mode. (Basic->Periodic->Consistency check->Inventory management->Item->On Hand). I do not expect it to help a lot to be honest.
      Most probably, the issues are caused by physical fragmentation of data/indexes of certain tables (inventSum/InventDim/InventTrans/InventJournalTrans). If you do not re-index them on regular basis (like once in a 2-3 months), try to re-index them and check for performance improvement. If you will see significant improvement, it means that you a) need a new DB server; b) need to reindex your data regularly. (Usually, reindexing can improve performance by 15-20%. If you performance increased more then that, it indicates that server is not making it up to real utilization).
      If you really have a lot of historical data, you can think about purchasing TWO disk arrays. One -expensive and fast – for current data; Another – cheap and slow – for historical data. Starting from version 2009, Dynamics AX support partitioning. If your database admin establish partitioning scheme for tables with large amount of historical data, Axapta will honor this partitioning scheme and won’t destroy it during synchronization.

      Regards
      Denis

  40. Hi,

    I am trying to run the Inventory closing but I am recieved an error of ” Negative cost amount for receipts is not allowed.” the infolog is showing “Inventory closing cannot proceed because available posted on-hand inventory on item ITM00001 is currently negative, which is not allowed according to its inventory model group.”

    Can you please help me.

    Thanks,

    1. Hello Khalid

      I have never seen this error message during inventory closing. First message can be displayed if you are trying to post inventory receipt with NEGATIVE cost price. Second – when on attempt to create dummy inventory transfer (for Average costing model), the system found that it is an attempt to issue more items when you have in inventory. I cannot tell you why it happens for your installation, but my first bet would be failed PREVIOUS inventory closing. When the system creates a dummy transfer for a period, it sum-ups quantity and amount from the period’s receipts and ADD quantity and amount from dummy transfer of previous period. It looks like your dummy transfer of previous period have some totally incorrect data in it. Try to cancel (in TEST environment of course), previous inventory closing and run it and subsequent inventory closing again…

      Regards
      Denis

  41. Hi, good day

    Hopefully I can help

    I have the following problem with an order (order returned) made ​​by a single item by the quantity of 50 say, to $ 1.7550 USD per piece with a total of $ 87.75 USD, when posting Packing Slip, the accounting entries are recorded by an amount of $ 92.75 USD with a slight variation of the original amount of the purchase order. Has someone has this happened? I understand that there may be an estimate on the price that can be adjusted in inventory transactions but the same happens in the accounting transactions? Why is this?

    1. Hello Carlos
      First of all: When you post purchase order return, the system uses regular instant issue cost price; The very same costprice which would be used for sales order. If you want this costprice to be equal to purchase cost price, you have to mark your return inventory transaction to purchase inventory transaction.
      Second, a posting of packing slips for sales/purchase orders, report as finished journals for production and other physical operations CAN create GL postings. But these postings are not intended for normal accounting. It is either accruals (which are made to separate GL accounts and are not always recorded in balance sheet report), either off-balance operations. In both cases, you should not care too much about amounts in these postings. They should just be a reasonable estimation for real cost price…

      Regards
      Denis

      1. thank you very much Denis!!

        How to mark your return inventory transaction to purchase inventory transaction ?? Just sorry I’m a beginner in this DAX ,I’d appreciate if you will indicate how to do this.

        1. In purchase order form (for return purchase order), click Inventory->Marking. Then find the inventory transaction being returned (inventory transaction of original purchase), fill Mark Now field with returned quantity and click Okey. After that your inventory transactions are marked, and cost price of issue will be equal to cost price of receipt (after invoice posting of course).

          Regards
          Denis

  42. Hello Denis,
    I have problem and I would be greatfull if you try to hep me. Problem is with Bom transaction calculation. After posting Bom Journal I run recalculation (to fix price) for BOM receipt transaction. I want to add production costs. I post recaluculation and after that all recalculated Items has got the same value and it is ok.
    At the end of the month I run inventory calculation and all recalculated Bom transaction are calcutlated again to value of BomLineTransaction.
    Maybe you met this problem before.
    Thanks in advance for your feedback.
    Piotr

  43. Hello Denis,

    I would like to ask if you ever encountered a scenario when you had to adjust the cost for the receiving warehouse for transfer order. I wanted to do this using adjustment form (from Closing and Adjustments form, I clicked on button Adjustments -> Transactions), but when I selected transfer journal, nothing got selected. I then checked the code and found that in class InventModelType method AllowTransactionAdjustment checks if it’s a transfer order or not. I then commented one line of code ( case InventTransType::TransferOrderReceive:) in order to be able to adjust the cost ad the time of receiving. Then I did some testing, made cost adjustments for that transfer order and it all seemed to work fine. I then added additional misc. charges to the original PO that I used to bring inventory to the issuing warehouse in the first place and then ran adjustment again and all the updates were done correctly.
    Since you wrote such an extensive article about costing, I was wondering if you may know if that is something that I could pursue (I couldn’t find an easier way to adjust inventory cost for transfer orders which is required because freight cost significantly increases product cost)
    Thank you very much!
    Mindaugas

    1. Hello Mindaugas
      I never tried myself to make similar customization, but I made analysis of the possibility of this customization for my friends. I am 99.9% sure that it can work. The designers of inventory costing module in Microsoft decided to prohibit transactions adjustments for transfer just because it is somehow does not fit into some interpretation of IAS/GAAP rules…
      You can enable transaction adjustment for inventory transfers (of various kinds) and you can extend markup mechanism to support inventory transfers. It should not have any negative influence on inventory closing…

      Regards
      Denis

      1. Thank you Denis – you are awesome – I kept my fingers crossed to hear this kind of answer 🙂

        Regards,
        Mindaugas

  44. Dear Mindaugas

    At one of my customer in AX- 2009, we ran Inventory Closing as on 30th June 2012, on 30th June 2012, at a movement it was stopped and through an error “Cannot create a record in Price (InventItemPrice). Item number: PM-0074, the record already exists”.

    I have read in some blog that if we unmark Latest Purchase Price and Latest Cost Price check boxes on item master this will resolve the above error, so I did that.

    At that time we did not notice that Inventory Closing was paused in process because In Invent Trans we could see Inventory Closing Transactions.

    Now next day we realized that we have to cancel Inventory Closing as some of the transactions were not posted, since Inventory Closing was paused in between so before cancelling I have to complete closing first.

    Now when I am resuming the inventory closing system is throwing error “The transactions on voucher ICV-1112-000001 do not balance as per 6/30/2012. (Company currency: -0.00 – secondary currency: 0.00)”

    Please help.

    1. Hello Muhammad
      Most probably, it is caused by roundings write-off. Please, check that all your inventSettlement records for the given inventory closing, which have balanceSheetposting filled, also have operationsAccount filled as well. If they do not have operation account filled, fill it with a job and continue inventory closing. Most probably, you have roundings write-off for purchase operations. These operations use Consumption account from Inventory posting setup for Purchase operation. In many cases, consultants forget to fill-out this setting. (It is not used in most cases and everything except inventory closing, works pretty well without this setup in most cases). But when inventory closing is being run, it uses this setting to define ledger account for roundings write-off…

      Regards
      Denis

  45. Hi Dennis,

    We have AX 4.0 with sp1. after closing a inventory on 04/30/2012 One item cost value in dimension report around 42K in local currency. At the day of 05/22/ 2012 i ran recalculate for that item and item cost value will changed even april month transaction value too. So i want to clarify that after completing IC for particular period even recalculation will change the cost values of closure periods.

    Regards,

    Rohana

    1. Hello Rohana
      Both inventory closing and inventory recalculation can change the value of inventory Transaction from closed periods. It happens when some of the past period’s inventory issues are settled to inventory receipts from current period. It can happen if inventory transactions are posted out of order. It often happens when stock arrived w/o accompanying invoice and you start to sell it today, but you post purchase invoice only 3-4 days later.
      In this case, inventory recalculation and inventory closing can easily change CostAmountAdjustment of closed period transactions. The same thing can happen also when you are revaluating past period receipt transactions (say – posting late inventory mark ups for past period invoices).

      Also, I want to mention that in this case, even though transaction itself is from past period, adjustment record in inventSettlement table has date of your last recalculation or closing. That’s why, strictly speaking, we should not use wording ‘cost value of inventory transaction’. It is better to use term ‘balance of inventory transaction per-date’. You should not just add inventTrans.costAmountPosted to inventTrans.costAmountAdjustment and use it as final value. You should take inventTrans.costAmountPosted for the transaction and then add-up all adjustments from inventSettlements for the transactions, made before reporting date.

      I consider that the field inventTrans.costAmountAdjustment is one of the most misleading features of logistics module in Axapta. Author of the module decided that an inventory transaction can have a lot of settlements (say – a 1000). He decided, that it would be much faster to take initial cost (inventTrans.costAmountPosted), add total amount of corrections (inventTrans.costAmountAdjustment) and then SUBTRACT adjustments AFTER reporting date, taken from inventSettlement. A peformance improvements from this method are questionable, but as a result of this design decision, 99% of consultants and developers simply add-up inventTrans.costAmountAdjustment to inventTrans.CostAmountPosted and then surprise that Axapta is changing the value of closed transactions…

      Regards
      Denis

      1. Hi Dennis,

        Thanks lot for your information. Normally after IC for particular period we generate financial value report for the inventory value report. ( IM> Reports> Status> Inv. Value> Inv. Value by Dimension report. )

        So it will give ware house item finanicial cost value. So after submitting it to the management then cost value will change means they will mislead when some body inquiry it.

        So if you can clarify How can we get exact value of inventory from AX report it self ? FYI from that day on wards that item cost is perfect.

        Apart from that is there any way/method to identify those king of item before we get a report from the DAX ?

        Highly appreciate your thoughts.

        Regards,

        Rohana.

        1. Hello Rohana
          First of all, you use correct report. It do calculate inventory value per-date. (So – I was wrong about my suspicions that you simply use some home-made incorrect report). The problem is: Probably you have some issues with inventory receipts/issues order. If you want to find exact reason for this discrepancy, you can try the following:
          1. Copy your production database into test environment and run inventory closing. (Not recalculation. Inventory closing saves settlements into persistent table and you can analyze your cost structure).
          2. Try to find issue or receipt transaction with changed value.
          3. For this inventory transaction, click Inventory->Cost explorer. This tool shows you the cost tree of every inventory transaction. You can check all inventory transactions/adjustments which contributed into the cost of this inventory transaction.
          4. Browse the whole tree. Most probably you will find that some of contributing transactions were either posted after 30.04, either adjusted after 30.04. This is the reason why one of your old transactions (closed by 30.04) were adjusted in May.
          The problem is: It is too time consuming to run this check every time when you need to find reason for the post factum cost change. Moreover, you cannot prevent situations like this from happening. Users post transactions with incorrect date; Receipts are posted after issues; All similar stuff happens every day. And every time it can lead to a change in cost price of the item, which had not had movements in the last month.
          So, I guess, you can just live with it. Better to have some standard answer for an auditor. Something like ‘our costs often are late and it causes sometimes to change in cost price of an item without movements in the given month’. You are not the only one who are facing this issue. Maybe Microsoft will fix it in future releases (I heard rumors that they are going to heavily rewrite inventory costing module in future versions), but maybe it won’t…

          Regards
          Denis

          1. Hi Dennis,

            Once again thanks for your valuable thought. will do it in test env. and will get back.

            Regards,

            Rohana

  46. Hi Denis,

    Thank you for this interesting article. You could write a whole book on this topic!

    I wonder about something. I believe the iteration that you explain is iteration within same level (self iteration). What about the outer iteration that read the next inventcostlist of the same item, would you explain what may cause that?
    I have item with transfer orders that has reached maximum iteration 100 for it self iteration, and also exceed the outer iteration of 100, yet the cost amount deviation is still high.

    Thank you.

    1. Hello Agus
      Most probable reason are loops in cost price. Most probably, if you check suspicious lot in cost explorer, you will see that some transactions have themselves as one of their cost contributors…

      Regards
      Denis

  47. Dear Dennis,

    Related to my above postings. Once you mentioned that closed periods cost values change due to abnormal issues order. So can you briefly explain it?

    I want to make sure one of clarification regarding AX 4.0 recalculation. If I know exactly one item cost is abnormal and then if i did recalculation for that item how adjustments were taken place against what transaction and so on. Is there any method or place to monitor that settlements and their references too ?

    waiting your feedback.

    Regards,

    Rohana

    1. Hello Rohana
      Abnormal issue order happens when your issues goes from inventory before related receipts are posted. Say, you posted first purchase order for 100 pieces on 01.06. Then you have posted transfer journal for 5 pieces on 25.05 (remember, on posting the system checks for negative inventory only on current date, so you can easily have a situation when your issue is posted before receipt).On closing of may, you would have your sales inventory transaction closed. (Because if would be fully settled to transfer journal receipt transaction). Then on closing of June, it might happen that your receipt from 01.06 was settled to your inventory transfer issue from 25.05. In this case, your sales inventory transaction would be adjusted in June, because adjustment of inventory transfer issue would be propagated to inventory transfer receipt and then to sales order inventory transaction…
      Regarding monitoring: First- you can check the content of inventCostListTrans table during inventory closing (I wrote something about it in my article). Another approach is to copy database to test environment, then run closing and use cost explorer to analyze cost sources for the given inventory transactions…

      Regards
      Denis

  48. Dear Denis,

    Спасибо за эту замечятельную статью!

    It helped me to put some light on this “black magic”. It looks like most people don’t really understand how it works and just trust and hope that the result will be OK.

    I have one short question:

    Do the flags “Physical stock” and “Financial stock” on a Dimension group already affect how inventory transactions are created by the system?
    Basically, I am talking about the InventTrans (which is the main basis for Recalculation, as far as I know?).
    Or do these flags only affect how Recalculation and Closing is done?

    Our problem is we realised one month after live-going, that our Dimension group misses these flags and we need to run Recalculation and Closing. So we are asking ourselves, if it will just affect the Closing or the situation is worse and our inventory transactions are already f***ed up.

    I would really appreciate your help!
    Waldemar

    1. Hello Waldemar
      First of all: Thanks for kind words!
      Speaking about changing physical/financial dimension flags after going live: First of all, you would probably find that for some of values of the dimension (say – you forgot to enable flag for Batch number), you have negative stock, because someone sold 5 pieces for a batch which has only 3 pieces. Physical Inventory flag, strictly speaking,do not influence costing. It only influence on-hand level check in time of physical inventory issues. Financial inventory flag DO affect costing, because it directly influence how instant cost price (in time of issue posting) is calculated and how settlements between issues and receipts is executed during final costing.
      In your situation, I would try the following (first in TEST environment of course): 1. Rollback inventory closing if you have any. 2. Disable check which prevents you from changing inventory dimensions setup. 3. Run inventory closing and check results. If results are more or less normal looking, and if you do not have so many messages about ‘Insufficient receipts’ during closing, you can check old transactions and change inventory dimensions for them. (Functions button in inventory details form). Cancel closing, change dimensions, run it again. After 2-3 iterations you will be able to fix all invalid data.
      If you have too many error messages after test closing – well, perhaps you still need to change dimension setup, but in this case, be prepared that for next couple of months, your per-batch costs will be very strange from time to time. Anyway – it won’t make situation any worst, because now you simply cannot calculate per-batch costs for all items and batches, but after you enable this setting, you will have reasonable costs for most of batches, and strange costs only for smaller part of them…

      Regards
      Denis

  49. Hi Denis

    I was wondering if you can advise why adjustments are calculated on transafer orders when running the inventory close.

    reason being, when we run our close Ax tries to post adjustments for transfer orders, and throws an error that no financial dimension exists, however you do not specify a fin dimeansion on a transfer order?

    1. Hello Gillian
      First of all: it is very normal for inventory closing to make adjustments to transfer. It is not very usual to make these adjustments as posteable to GL, but it can happen when the system writes-off roundings or hanging adjustment during final iteration of inventory closing. In this case, system takes dimensions from related records in inventTransPosting table (you can see it in Dimensions tab of inventory transactions for the transfer). In turn, during posting, the system fills-out inventTransPosting.dimension field with dimensions specified in item details (inventTable) for the item in transfer line. So, to correct the situation you need:
      1. Make sure that all your items has reasonable dimensions in inventTable
      2. Write a job, which will run over inventTransPosting records for all non-closed inventory transactions of transfer orders and fill inventTransPosting.dimension with relevant information from inventTable.

      Regards
      Denis

  50. Hi Denis,

    I refer to my comment at November 9th, 2012 on 3:06 pm.
    Just wanted to give you an update, maybe it is interesting for you.

    We have some luck, that we didn’t run any closings or recalculation yet. So:

    1. We decided to postpone the closing
    2. We created new Dimension group with “Physical inventory” and “Financial inventory” set for Batch (before, the Batch was activated and mandatory, but didn’t have these flags)
    3. We assigned the new Dimension group to items using code and ignoring MS checks.
    4. We run Recalculation just for a subset of items and the guys in accounting are happy with the results.

    I am still not sure, we will have any problems (or maybe differences in general) while daily working and posting on sales orders.
    But from your reply I understand that we will:
    “Financial inventory flag DO affect costing, because it directly influence how instant cost price (in time of issue posting) is calculated”

    So we are going to test some more (Closing etc.) and do the closing in December.

    I will update here; I hope our case will help somebody who has similar situation.

  51. Hi Mindaugas,

    Thanks for the wonderful article. It helped us a lot.
    I need your advice on service items. In the item model group, we have now stocked product as a field. Should that be checked in general for service items? What’s impact if we check it or not?

    I came accross this issue because for AX for Retail. if i want to do any transaction for a service item, then that item should have stocked checked in item model group. Stocked product unchecked items doesn’t appear in POS.

    I created a service item with stocked product checked, did some transactions. Although there are no settlements, but still i am not sure what is the best way out for service items.

    Thanks in advance.

    Pranav

  52. Thank you for the article! It really helped me.

    The line at first confused me: “4. Then this two pieces was transferred back to wh2 on the 6th day of the month. (Trsf2)”. I think it should be “4.Then this two pieces was transferred back to WH1 on the 6th day of the month. (Trsf2)”

  53. how to setup standard cost revaluation base on batch number? i tried to select physical and financial posting in dimension group but unfortunately it only display until location level in standard cost transaction inquiry screen. any idea to include batch number for each revaluation record.

    1. Hello Jarod
      As far as I understand, standard costing support price storage and calculation only per item dimensions. I guess, the best approach for you would be to add another inventory dimension (item dimension) and use it for costing purposes. Anther solution – you can simply use one of the existing item dimensions as a replacement for this batch costing dimension. (I guess, your ‘batch’ is not usual batch. You are trying to use it for other purpose).

      Regards
      Denis

  54. Hi Denis,

    As stated in my previous comments, our problem was that we had a wrong dimension group.
    AX didn’t allow to change the dimension group for items which already have transactions. But in our case we haven’t run any recalculations or closing in our system.

    I just wanted to give you a short feedback:
    We changed the dimension group using a Job. After 2-3 months we don’t notice any problems with that.

    The original idea was to purchase/sale with batches and allow to calculate costs per batch. In most cases it worked. In some rare cases users did mistakes by not using batches.

    Regards,
    Waldemar

  55. Hi Denis,

    During inventory close, system is throwing an error that the account number for sales order consumption is not defined for item 90100. I checked the item and consumption account is defined in the item group. I also checked transactions and found that consumption entry is posted on sales order transactions.

    What could be the issue? Please share your ideas.

    Thanks,
    Ashish

    1. Hello Ashish
      Inventory closing takes accounts not from current inventory posting setup and not from GL voucher. When IC is creating the records in inventSettlement, it fill-out account numbers with historical account data from inventTransPosting table. Try to check all Sales inventory transactions for your item. Check content of Ledger tab. If for one of the records you will see empty field Accounts-Financial->Operations account – that’s it. You have cancel your closing, fill-out this field of this record (or several records) with necessary data and then run closing again.

      Regards
      Denis

  56. Thanks Denis for your reply.

    I looked in inventtransposting table for the concerned item and found that the ledger account rec id was present for all the records of sales order consumption entry. I am not getting what could be missing here. Please advise.

    Thanks,
    Ashish

  57. Hi Denis,
    When the recalculation/closing is done, is there a table where AX maintains the lot id which was used for settlement purposes? When I purchase the goods, there is a lot id that is generated and I want to find out that lot id, when the recalculation/closing is executed after the sale has been done and the cost has been adjusted, so that I can generate a report out of the system to show that which was the lot id that was used for the settlement purposes?

    1. Hello Vamsi
      You can use ‘Cost explorer’ tool for it. I wrote about it in this post: http://fedotenko.info/?p=123
      I do not think that you can print it, but you can use it to analyze the sources of cost of the particular inventory lot.

      Regards
      Denis

  58. HI Denis,
    We are attempting a month end inventory recalucation and we are receiving the following error:
    “Recaluculate Inventory
    – Item FLOTANSTA
    – No account number exists for acccount type cost for project

    Any ideas???
    Your help would be greatly appreciated,

    1. Hi Chelsea,

      I guess item FLOTANSTA has a project category associated with it and also has been used for project transactions.
      I would review AX project module- posting profiles, especially with posting type ‘Cost’ related to the relevant project item transaction.

      I hope, I’ve made sense.

      Regards,

      AJ

    2. Hello Chelsea
      From one side, I can agree with Alison on her advise. Probably, in the closed period, you have adjusted inventory transaction with project id. During posting, system is trying to find project cost account and fails for some reason.Try to:
      1. Run inventory recalculation, wait till it stops with error message
      2. Run a query which will show all projIds for inventory transactions adjusted by your recalculations for your item.
      3. Check that cost account is defined for all projects and your item.

      From other side, I could not find any place in standard inventory recalculation and posting algorithms, which would try to fetch the ledger account from project posting setup. Perhaps – we are dealing with a customization to standard functionality…

      Regards
      Denis

  59. Hello Denis,

    By accident, we have a Inventory Closing Cancellation stuck with InventCostStatus == Calculation. Resuming isn’t available as an option.

    The cancellation voucher has settlements, but has never posted. Any ideas on what we should do to fix this ?

    Regards,

    1. Hello Philip

      Just go to your original inventory closing and click ‘Cancel’ again. When you are canceling inventory closing, the system first tries to find and continue existing (terminated for some reasons) cancellation closing and continue it.
      So – instead of clicking ‘Continue’ on cancellation closing, just cancel your original closing for 2nd time. (As I said – it will continue old cancellation, not create new one).

      Regards
      Denis

  60. Hi Denis,

    Does the running average cost (average cost before close/recalc) include Marked quantity or Marked cost price? Since the marked transactions can have varied Costs, we were thinking that these are not considered in the running average calculation nor in Inventory close process. Can you please comment on this? Right now, the average cost calculation is including these marked quantity transactions.

    1. Hello Madhu

      Normally – both closing and recalculation process marked transactions in separate stage – in the very beginning of calculation, before doing anything else. On success, these marked transactions are tagged as closed and, subsequently, are ignored during normal average/FIFO/LIFO cost calculation process. Nevertheless, if you have wrong markings (say – markings between inventory transactions with different set of financial inventory dimensions), these markings are being cleared during marking processing phase and these improperly marked transactions are later processed during normal averaging/FIFO/LIFO phases….

      Regards
      Denis

  61. Thanks Denis for your quick reply. Everything canceled correctly and the suspect month has been re-closed.
    The Closing and Adjustment Form by default initially shows Active, so the issue had gone unnoticed for 30 days until we tried to do our month-end Production Order Costings. Trying to end the Production Orders alerted us of the closing issue.

    Thanks again for the help.

    Regards,

    Philip

  62. Hi Denis,

    thanks for sharing your insights on AX 2009 inventory closing it helps me a lot in analyzing the postings and update made by the system after Inventory closing procedure.

    question, do you have any idea were we can get the total amount with posting type “Weighted average inventory closing” which updates only General Ledger Account and does not update Item Cost? I find out that some of the entry made by the system after inventory closing in General Ledger Account does not update the Item Cost level and only the related inventory Ledger account that results to difference between inventory value and ledger account.

    Also, can you list down the name of the table in AOT being updated after inventory closing? and those table that can be used to get the total financial updated items, physically updated items, item issuance that are settled during inventory closing and adjustment made by inventory closing…

    hope you can help me.

    thanks,

    Ramil

    1. Hello Ramil
      The situation you are describing is very strange. Normally, GL postings of inventory closing are created from inventSettlement table. And update to inventSettlement is causing update to cost fields in inventTrans. I can imagine only one explanation for your situation: After inventory closing, someone run a job, which destroyed some of the settlements or transactions.

      Another possibility is that you run several inventory cancelations. IC cancelation code for Weigthed average model has a bug, which was fixed in DAX2009 CU6 (or CU7) andreintroduced in DAX2012 (in DAX2012R2 it is still in place). Inventory cancelation deletes dummy tranfsers, in turn deleting related inventory settlements. Since posting to GL happens afterward, it might be that GL voucher from cancelation is not equal to GL voucher from closing. As a result, your GL balance after cancellation can be different from the one before inventory closing. (Usually difference is not so big, because adjustments for dummy transfer transaction are not made too often, mostly in case of roundings/errors write-off).

      The list of the tables updated during inventory closing is very short: InventTrans,InventSettlement,InventCLosing,LedgerTrans,LedgerBalancesDimTrans,LedgerBalancesTrans. If you use new standard cost mechanism, it can also update inventCostTrans,inventCostSum, inventCostVariance tables. During inventory closing, it an also add/delete records to InventCostList and inventCostListTrans tables, but ther are working tables and should be emptied by the system in the end of closing…

      Regards
      Denis

  63. When the clositng routine was executed the system gave me this error ‘Dimension Cost Center must be specified for account 418100.’

    I have made the Cost Center mandatory with this ledger account. I want to know why the system is asking me to specify the dimension . is this because of settlement entry the system wil throw ?

    1. Hello Ahsan
      During inventory closing/recalculation, dimension for posting goes by the following chain: InventTransPosting->InventSettlement->LedgerTrans. If one of the settled or adjusted transactions is from previous periods, when you had not had ‘mandatory dimension’ checkbox set, this dimension might be empty in inventTransPosting. As a consequence, this empty dimension is copied to inventSettlement, later the system is trying to post ledgerTrans with empty dimension.
      You have to options to fix it:
      1. Make job which will fill-out all old inventTransPosting dimensions with some reasonable values.
      2. You can just clear ‘Mandatory dimension’ checkbox on next several inventory closings, so old historical data could be processed without too much problems.

      Regards
      Denis

  64. I am having the below error while running the Inventory Close Procedure

    ‘Item number: 000003
    Transaction: Sales order CC042227, lot ID 00268434_098
    Unit cost price 419,39 can be wrong as the transaction cannot be fully settled’

    any know reason for this ?

    1. Hello Ahsan
      It means that you sold more than your purchased. 🙂 More specifically – some of your issue inventory transaction do not have related receipt inventory transactions. It is not very good, but later, when you post delayed purchase invoices, next inventory closing will adjust issue cost price of your current period issues. (But with dates from next period).

      Regards
      Denis

  65. Hi Denis,

    Quick question, in AX 2012, if you run a inventory close as of Feb 28, 2014 and it not been run for several months, what dates are the ledger postings made for? Since we have released statement for prior months, we do not want the system going back and making postings to those previous months.

    Thanks,

    1. Hi Aquino
      General ledger postings (as well as inventSettlement records) are always created with the date of running inventory closing/recalculation. So if you are running IC for 28.02.2014, this date is used even for adjustment made for inventory transactions of previous periods (Jan,Dec,Nov etc)
      Regards
      Denis

  66. Hi,
    How to prevent instant sales order inventory issue transaction. This only required to be calculated and go in transactions on month ending inventory closing process.

    I already did it with a simple code comment in ax 2009 but now i cant find the place of the code.
    Hope you can help me on this issue

    Thanks

    1. Hello
      Try to redefine inventMovement.financialIssueCostValue() to return 0 for this item. If you also want to suppress similar posting for physical operations, modify InventMovement.estimatedPhysicalValue().

      Regards
      Denis

  67. Hi Denis,
    Thanks for sharing this article, it really helps me a lot to understand these topic.
    I have a problem and hope you can help me. We ran a recalculation of the whole inventory, wich after a while send a message that it had reached its maximum iterations. After 2 months we noticed we had differences between inventory and accounting, and traced it back to that recalculation.
    What we noticed is that the process had made an adjustment on the inventory of $353.00 from one article, but it hadn’t updated to GL which caused the difference. Could it had been because the process stopped because of the iterations?

    Sorry for my english i’m not very good at it, hope you can help me!

    1. Hello Daniela

      Most probably, you are facing consequence of old bug which was fixed in the very last RollUp for DAX2009 (RollUp8, as far as I remember), but reappeared in DAX2012. It happens when you work with average inventory model. When you close inventory with average model, it creates dummy transfers and, potentially, adjustments to these transfers. When you cancel recalculation/closing, it deletes transfers and adjustments for these transfers. The problem is: before RU8 these adjustments were deleted BEFORE posting stage. So, from time to time, you can witness a situation when voucher for closing and voucher for cancellation differs to some extent. (Luckily, it happens not too often, because, normally, adjustment for dummy transfer are not posted to GL. But in some cases- say when system writes-off rounding in the end of calculation, system can create, as an exception, posteable adjustments for dummy transfer).
      Another typical problem – if your recalculation ends because of reaching maximum iteration, it often can lead to different cost price in issue and receipt parts of transfers. Normal closing, if it adjusts issue part of transfer and then terminates because of iteration exhaustion, writes-off this adjustments as a rounding error (and issue and receipt costs price remains the same). Recalculation never writes-off rounding errors, it simply adjust issue part and does not adjust corresponding receipt. As a result – you have hanging balance on the receiving warehouse. (and subsequent inventory closings do not adjust it). I try not to use recalculations at all. If I really need it, I simply enter high enough number of iterations- say 300. (Yes – By default, closing/recalculation does not allow to enter more than 99 iterations, but this check can be easily disabled).

      Regards
      Denis

  68. Hi all,

    I have a ‘newbie’ question, although it has puzzled us.

    Is it possible or advisable/inadvisable to run the close procedure at the same time as stock is being processed elsewhere – for example, at the same time as a replenishment batch job is being run, or the warehouse is performing goods in transactions and so on?

    We are looking for a time to run close when the system is relatively quiet, but it’s a struggle to identify a time when there are no stock movements, given we have 24*7 website sales and so on.

    Thanks in advance,

    G

    1. Hello
      It is possible to run stock closing anytime. Of course, inventory closing seriously increase load on AOS and SQL Server, so it is better to run it in the time of lowest user activity, but nevertheless normally it should not prevent system from operation. Every helper thread can lock maximum 100 inventory transactions and about 200 inventory settlement records. In the end of transaction, it can lock inventory on-hand data for processed items for a second or so. So – in the worst case, it can block normal logistic users for several seconds. The only big exception is InventSumLogTTS table. Because of design bug, Inventory closing/recalculation inserts data into this table (totally useless data) and any attempt to update explosion, run dynamic plan update, run production order scheduling can hang for much longer then several seconds, because if might need to wait for every helper thread. In my last post I wrote how to block inventSumLogTTS update from inventory closing, so if it will become a problem for you- you can just block this functionality.

      Regards
      Denis

  69. Hi,

    We are getting the error when we run inventory closing,
    ———–
    Item Number XXXXX
    “Value of department = is not allowed in account XXXXXX”..
    Posting cancelled
    ——————

    I understand from the user that they have posted adjustment journal by updating department dimension for one item and after adjustment they have chnaged it back.

    Able to trace the transction which was posted with department change.. Not sure how to get rid of this error..

    Is it possible to skip this item from inventory closing?

    Or is there a way we can skip only this transaction from c;losing?

    appreciate your inputs..

    Thanks,
    Nirmal

  70. Fantastic artical Denis it has actually cleared my All doubts I am having in IC and adjustment procedure
    Thanks
    Vikrant

  71. Hello,
    I was desperately looking for this stuff.

    I appreciate your effort to drill down this Complex Concept.

    Thanks,
    Kumkum

  72. Great explanation!

    One correction:

    the 18th should be settled to the (not) issue (but receipt) from the 31th…

    in the following paragraph:

    It would be more correct to call LIFO and ‘LIFO on date’ models a LILO model (Last In Last Out). It is a kind of direct opposite to the FIFO model. System runs through the list of unsettled issues beginning from the last one, and settles them to the last unsettled receipt (ordered by the date of course). A “LIFO on date” model differs from the LIFO in the aspect of finding an appropriate receipt for the given issue. This model settles an issue to the last receipt that have been received BEFORE date of the issue. Say, we purchased some item on the 1st, 5th and 31th days of the month and this item was sold on, say, 10th and 18th of the month. From the strict LIFO point of view, the issue from the 18th should be settled to the issue from the 31th. (Because LIFO suggest the last receipts bound to be written-off first). But the whole idea seems to be a little weird, for we are trying to issue the item long before it was actually received. So it seems that developers have implemented two similar models to satisfy both accounting and common sense approaches to the LIFO principle.

  73. Hi

    Our client is using fixed receipt price and weighted average as their inventory costing model.

    When running the close we have quite a large number going to the inventory adjustments account. When analysing this I found this to be due to quarantine orders, transfer order receipts and transfer order shipments. I am not really expecting financial postings related to these transactions. The amounts are actually small per transaction, but the volumes of transactions are high, therefore the total amount is also quite high (£20k).

    I would have also expected cost adjustments and overall costs for a particular quarantine order / transfer order to net off as I would have thought that these transactions are marked together. But this is not the case.

    Do you know why this is happening?
    Do you know of a way we could prevent it from happening.

    Thanks
    Jenny

    1. Hello Jenny
      Sorry for delay with answer. Fist of all – if you work with weighted average inventory model, it is pretty much settle your inventory with ‘All open receipts to all period issues’ principle. It does not matter, whatever it is real ‘costing receipt’ (like purchase or production), or just some ‘transfer receipt’ (Like quarantine order or transfer journal). It is just the way the system was designed.
      Nevertheless, latest cummulative updates of DAX2009 and DAX2012(from initial release) introduced new checkbox in Inventory Parameters: Close Non-Financial transfer. If this parameter is enabled, then all transfers, which does not change financial inventory dimensions (and, thus, do not influence cost price) are excluded from cost calculation and forcibly marked as closed in beginning of inventory closing.
      You can read whitepaper about it here.
      NB: In DAX2012R2 and DAX2012R3 this feature is enabled by default (I am not sure about Release 1). In DAX2009 this feature was implemented in RU7, but it had very dangerous bug, which was often causing data lock during inventory closing. If you are going to use it with DAX2009, make sure that latest rollup was installed (RU8 at least). Also – before using this feature in DAX2009, run couple of inventory closings with this feature in test environment and then show results to your accountants.

      Regards
      Denis

  74. Hi,
    My client, running on AX 2009, is currently trying to perform an inventory closing for the first time. The problem is that we run into the issue with lack of memory which makes the server ultimately shut down. This occurs in the final step of the inventory closing procedure when the GL-transactions are created.

    I should mention that the inventory closing runs for about 40 hours before reaching this step which is due to the fact that they have never closed the inventory before and they have transactions going back almost 10 years. We have successfully performed the same procedure for two companies which are smaller but with the third, final and largest company, the memory runs out. In all three companies, they always sell their items before financially updating the purchase order and in some cases, they use BOM items to create kits of items that are then exploded on the sales order.

    We have of course looked into reducing the number of threads and the maximum buffer size. We are not using any helpers and we have tried to increase the memory. Nothing seems to work.

    Now to my questions:
    * After reading you comprehensive and excellent text on this subject, I am thinking about reducing the Maximum throughput and increasing the Minimum throughput adjustment. Do you believe this could reduce the memory needed for inventory closing procedure?
    * I am also looking into the possability to run an inventory recalculation first for the whole period and then do the inventory closing. You mention that running a recalculation can reduce the size of the job . Could this perhaps help our situtation?
    * Do you know any other way to reduce the amount of memory needed to perform an inventory close procedure?

    Best Regards,

    Rebecca

    1. Hello Rebecca
      First of all – the issue have very little to do with inventory closing itself. Somewhere in the end of closing procedure, system just process all created inventory settlements, creating equivalent ledger postings. Before end of this step, system keeps all created ledger postings in memory. It happens regardless of any setings related to number of helper-threads (because it is a one-thread process) or number of iterations/max throughput correction (because this setting do not influence number of inventory settlements). Most probably, you are running 32bit version of DAX2009 AOS server, which can use only 2 Gbytes of available memory.
      So – most obvious solution for the problem is to install 64bit version of AOS. Even if you manage to tweak closing into running in just 3 Gbytes of memory, I am sure that in future your customer will face various issues because for realistic usage scenario 32bit AOS and 2 Gbytes of memory just is not enough.
      As a short term solution, you can try to run inventory closing, with Specification parameter set to ‘Total’. In this mode, system creates just one ledger posting for combination of account+dimension, thus decreasing amount of RAM, required to store all ledger postings created during posting phase. (It you set this parameter to Item or Item Group, it creates separate posting for every item+account+dimension or item group+account+dimension comination).
      Also – if you do not use Period Average inventory model, you can try to run several inventory closings instead of one (say – inventory closing for every 3 or 5 days period). In this case, number of inventory adjustments and number of resulting ledger postings per closing will be smaller and you have better chances to finish closing without running out of memory.

      Regards
      Denis

      1. Thanks Denis!

        You are absolutely right. The client have a 32bit version of the server and we are looking into the possability of installing a 64bit version instead.

        Thank you for the explanation though. It really helps to understand why AX needs so much memory during this process. The issue here, besides the server, seems to be the dimensions. The client has set up a specific dimension value for each item which means that I cannot utilize the “Total” parameter since it gives the same result as the “Item” setting. The combination of account + dimension is the same as account + dimension + item since the dimension value is always the same as the item number in this perticular environment.

        Thanks to your answer, we can now definitely say that it is the server that is the problem.

        Thank you!

        Best Regards,

        Rebecca

  75. hi Denis, first of all, I would like to thank you for the great article. It has been very helpful for me to understand how inventory closing works.

    My company runs on AX2012 R2 CU7. Last month, we ran the inventory closing (used batch job with batch helpers). The closing completed without hiccup and error. What came to surprise is that when I check the ledger voucher in the inventory closing form, there is no record generated. Then, I check in the inventsettlement, there are records with posted status. This has cause the subledger and ledger not tally.

    I also checked the log for the “posting to ledger” batch task, the is a info message
    “This inventory closing or recalculation has reached the maximum number of iterations. For a more accurate result, consider increasing the maximum throughputs parameter.”

    I think this should not cause no posting to ledger.

    Do you know what could possible cause this to happen?
    How can I correct this?
    Do you know of how we could prevent it from happening?

    Thanks
    Earnest

    1. Hello Earnest
      I never met similar situation before. I think, it is caused by one-off bug somewhere in ledger posting code. If you do not use standard costing, I would suggest you to try the following (of course – in test environment first):
      1. Rollback inventory recalculations/closings, made after problematic inventory closing.
      2. In the problematic inventory closing, set InventClosing.InventCostStatus to InventCostStatus::Calculation;Set InventClosing.active to Yes. (develop a small job for it).
      3. In all InventSettlement records for the problematic closing, reset InventSettlement.posted to No.
      4. Select problematic closing in inventory closing form and click Close Procedure->Close. Do not run it in batch mode; Ledger posting is single-threaded process anyway and it is easier to get meaningful error message in interactive mode (especially if we are dealing with some non-trivial problem in kernel).
      The idea behind is that steps 1-3 should restore state of closing procedure of pre-Ledger posting stage. When you are trying to continue active closing, it should just go to Ledger Posting stage and then to inventory recalculation stage(if it was specified in inventory closing parameters). I am not 100% sure that this approach will work (I have not tried it in DAX2012R2 where posting parallelization logic was changed a bit), but chances are high…

      Regards
      Denis

      1. hi Denis,

        Thanks for the quick response.

        I tried to simulate the closing with similar DB in test environment and it generated the GL posting. The difference between production environment and test is test environment has AOS, batch and DB in 1 machine whereas the Production they are in different servers.
        I’m not sure you can figure out something from this.

        Now, I’m trying another simulation where I separated out the batch server. Let’s wait for the result.

        regards
        Earnest

        1. Hello Earnest
          This behavior only support the idea that your initial issue was a result of one-off problem. Just in the moment of posting, your AOS went crazy for the moment and managed to loose all in-memory copy of GL data, before writing it to database. You can try to reproduce the issue again, but I do not think that we have chance to witness it.

          Regards
          Denis

          1. hi Denis,

            It has been a while. Just like update everyone on this issue.
            I managed to identify the root cause of this issue. It was due to application code not sync between AOSes.

            This can be resolved by doing a proper model store file deployment. (full CIL + restart AOS)

            Thanks for the help given.

            regards
            Earnest

  76. Hi Denis,

    Thank you for this great article.
    Hope you could help us with our problem on GL and Inventory discrepancy. As an overview, our company is using AX 2009 and weighted average by date costing method. Upon tracing, we found out that the discrepancy occurs during Inventory Recalculation and Reference is Quarantine Order. But we do not know how to trace transaction anymore. hope you could help us. what do you think is the reasons of the discrepancy.

    thanks a lot.

    regard,

    Richel

    1. Hello Rachel
      Most probably, you are talking about well known bug in inventory recalculation. It cause situations when after recalculation, issue and receipt of transfer (of any kind – including transfer journal, transfer order, quality order and so on) have different cost price. There is no easy way to bypass this issue. You can try to escalate the issue to Microsoft, but I doubt that they will be ready to fix it (especially for version 2009).

      Regards
      Denis

  77. Hi Dennis,
    Many thanks for such a detailed blog related inventory re-calculation / closing.

    One of our client is running AX2012 R3 RTM. We went live on 01-04-2015 and since then we are facing one or the other issues in inventory costing. I have two questions related to this topic.
    1) On 01-02-2016 I ran inventory recalculation process and it took 6 days to complete. In between, it stopped 2-3 times and I resumed it every time. Finally after completion, when I checked in the ledger, I saw “X” amount is posted but settlement screen shows “Y” amount. What would be root cause of this and what could be done to correct this?

    2) Because of some not ended production orders, I am not able to reconcile the inventory ledgers with Inventory Value report (Inventory Management –> Reports –> Status –> Inventory Value –> Inventory Value).

    Regards,
    RV

    1. Hello RV
      1. As it seems, you have some infrastructure issues.Maybe your CIL is not properly compiled, maybe your batch server is crashing, maybe there is something else of that sort.At least – regardless of any mistakes in your transactional data and application setup, closing should run without breaks.
      2. It is very normal if amounts are different between inventSettlement and resulting GL voucher. If inventory closing is adjusting non-GL related operation, like quaraintine order or inventory transfer, it does not set Inventory Posting and Operation posting fields in inventSettlement. In this case, this record is excluded from posting to GL.Please, try to re-check your situation, excluding inventory settlement records with empty posting fields
      3. Unfortunately, in current version there is no solution for the issue. If your production scenario does not have transitional WIP in the end of month, you can split your production order, end and calculate finished part and leave remaining production in the second production order. If your production is naturally long enough (like wine making for example, where consumed grapes must spend months in bottles/barrels), then you are out of luck. in current version of DAX there is no mechanism to support transitional WIP….

      Regards
      Denis

  78. Hi – great commentary and very informative.

    One thing I’ve not understood is the worked example at the start. Where does the price of 220 come from.

    I am at a loss as to how this is arrived at. I can get to 225 by taking the first item @200 and the second @250 and averaging them, but how does the 220 derive ?

    Also, could you possibly clarify the origin of the 40 adjustment ? Is that the markup allocation referred to in point 6 ?

  79. Hi Denis,

    Thank you for creating this resource, we used your information to MRP to good effect 🙂 Now the time has come to understand cost postings…

    We are using standard cost in our retail company, and periodically the users update the standard cost against the item. When this occurs, we are seeing some odd cost amounts posted on InventTrans. I.e:

    Item A has a standard cost of £40.39 up to 11/09/2017
    Item A has new standard cost of £44.43 from 12/09/2017 onwards

    After this price update:
    A sales order line transaction for item A has a “cost amount” (method – InventTrans.CostValue()) of £8.07! The CostAmountStd (fixed receipt price value) is also £8.07. An adjustment has been automatically made for £36.36 to increase the cost back to the new standard cost of £44.43

    A similar occurence happens on a Transfer Order Shipment transaction line. But here, the InvenTrans.CostValue() and CostAmountStd returns £-8.07, with the adjustment showing £36.36.

    Other transactions against the same variant of item A do not have the same adjustments being made.

    I can’t for the life of me work out what has happened to cause these postings, other than noticing that (44.43 – 40.39) is exactly half of £8.08 (assuming some penny rounding).

    Is there any reason you can think of that would cause these postings?

    Hope to hear from you!

    Kind regards,

    Omar

    1. Hello Omar
      I do not completely understand why do you mean by “A sales order line transaction for item A has a “cost amount” (method – InventTrans.CostValue()) of £8.07! The CostAmountStd (fixed receipt price value) is also £8.07. An adjustment has been automatically made for £36.36 to increase the cost back to the new standard cost of £44.43” Does it mean that after you changed standard cost, cost price for the inventory transaction became 8.07GBP ? Do you mean that it was 8.07GBP in the beginning, but after you changed the standard cost, it was re-valuated into the new standard cost ? If we are talking about the first case, then it is some mysterious bug in standard costing. (I never heard about something like this). In the second case we are also talking about a bug, but not so mysterious (probably something of one-off type). For some reason (say – your database server had a bad day), system fetched wrong standard cost during initial sales invoice posting. Later, when you changed cost price, it ran through all sales invoices posted after the price change date and re-valuated them to the new standard cost. It does not check whatever the old standard cost was a correct one, it just calculates and post the difference between the current cost in the inventory transaction and the new standard cost. So – it is a bit strange how did it end up with original issue cost of 8.07, but since it has not happened since then again, then it is better to hope that it is just a one-off bug. But what happened afterwards – is a totally normal behavior of the system.

      Regards
      Denis

  80. Hello Mr Fedotenko,

    I have a questio for you.
    Model is weihted avarage
    Ax Version 2012 R3
    Inventory model: Include physical transactions

    In my client the invoices come after months after packing slips. So we include physical inventory.

    Lets think there is just one purchase and one sales for an item. When we make closing, system makes settlements for the sales according to the physcal value on inventtrans. And settle the sales transaction.

    After closing month, the purchase invoice comes next month with different amount than the physical value.

    Then we close the following month again.

    In this scenarios system will not make adjustment to the sales. The sales will remain the cost amount of the physical value. Do you have any suggestion to me?

    Best Regards

  81. Hello Denis!
    In my company we have a procedure to run inventory closing in company CDE first and when it is ready, we start process in company CPL. But now we have a requirement to cut total time of closing. First what we would like to implement is to run closing in bot companies at the same time.
    Do you have an experience with running inentory recalculation in two companies in AX simultaneously? Do you know any reasons we should not do it?
    We use AX 2009 SP1 RU8.
    Thank you in advance.

    1. Hello Tomasz
      Well – I would try to run them in parallel. I do not see any reason to have deadlocks or something similar during this parallel closing. Maybe you should try to disable update of inventSumLogTTS during closing, but as far as I remember, it is already disabled in your installation.
      Regards
      Denis

      1. Hello Denis,
        OK. I will try it next month.
        Thank you for your answer.

        BR,
        Tomasz

Leave a comment

Your email address will not be published. Required fields are marked *