Author Archives: jgracak

Catch weight items

Catch weight items are used in industries where the same product can vary in size and/or weight. A common example is food industry where each fruit or meat piece can vary slightly in weight. Lets take a look at some more extreme examples like scrap cars and vehicles that are processed in a scrapyard.

Before we start there is an important distinction between full and partial visibility for catch weight items. It boils down to this:

  • Partial visibility – Receiving multiple units of the item in a batch. In this scenario we do not know the weight of each individual unit
  • Full visibility – Receiving each unit of the item separately and tracking it in inventory. This scenario is what we need for scrap cars

The item model group should be set for Serial number and Serial number control. We will put the vehicle serial number in it.

Item_model_group

The most important settings when opening the item are:

  • checking that the product is CW
  • Choosing the correct tracking dimension
  • Inventory, Sales and Purchase units which is the primary unit

create_item

We need to setup a new unit “Car” which will be used for CW.

CW_unit

Now we need a unit conversion between units. Since we choose “Mass” for the Unit class, it will be an “Intra-class conversion”. The conversion will be 1 Car = 1200/1 kg. This will be the default value if we put a 1 Car unit on any form.intra-class_conversion

Now we are able to choose the CW unit “Car”. We can also put in the minimum and maximum quantity for the CW unit in kg (1 and 50.000 kg respectively).

CW_Unit_setup

All we need to do now is to purchase and receive the items. On the purchase order line we input a CW quantity of 3.

PO

Under product receipt registration form we need to input a line for each vehicle. Additional we add the serial number and actual weight of vehicles.

Product_receipt

After we register and receive the items we can see them in inventory. Inventory overview for standard unit of measure (kg).overview_kg

The overview in CW unit.

cw_overview

This is all there is with catch weight for basic scenarios.

Until next time, keep on DAXing!

 

Book review: Using Microsoft Dynamics AX 2012 R2

Instead of a traditional blog post this will be a book review. Using Microsoft Dynamics AX 2012 Updated for Version R2 is a great book to learn the basic knowledge on how to handle all basic business processes in Dynamics AX. For every process there is a brief explanation with the required steps but not much details on all the possibilities. This is not a bad thing, otherwise the book would be thousands of pages long and that is beside the point. It is a great pocket reference for every time you need to quickly check out what is possible, not possible or available.

The book does not go into every possible module but rather goes into the major ones like:

  • Navigation and User Interface
  • Supply Chain Management
  • Trade and Logistics
  • Manufacturing
  • Financial Management

I would recommend it for all experienced and new users of AX even just to quickly check out the features and capabilities in the modules you haven’t worked on yet.

PS: As I found it myself, this book can also be a great conversation starter.

Advanced Project Setup (Part 1)

A little introduction

AX 2012 R2 Project setup can be hard and confusing. If you are trying to set up anything other than a basic project you can get lost pretty fast. In this blog post I have compiled a list of possible advanced project types in AX 2012 R2 and the required correct ledger posting setup for each one. In the next blog posts we’ll talk about other required setups.

The basic project types are:

  • Time and material
  • Fixed-price
  • Internal
  • Cost
  • Time
  • Investment

We will explore advanced project types for time and material, fixed price, internal and investment projects.

The ledger posting setup defaults are set up in Project management and accounting -> Setup -> Posting -> Ledger posting setup

PSOT_004_Ledger_posting_Setup

The same set up can be found in Category groups, Project categories and Project groups which will override the default ledger posting setup.

Advanced Time and Material Projects

There are two types of advanced time and material projects:

  • Time and material projects with Work in Progress (WIP) – Used when there is an extended period of time between when costs are incurred and when the costs are invoiced
  • Time and material projects with accruals (WIP sales)

Time and Material Projects with Work in Progress (WIP)

The following cost accounts need to be set up:
[table]
Cost Account,Ledger,Purpose
Payroll allocation,P&L or Balance,Credit hour costs.
Cost,Profit&Loss,Debit all costs.
WIP cost,Balance,Debit costs. Credit when invoice updating.
Cost item, Profit&Loss,Credit item costs. Debit when invoice updating.
WIP cost items,Balance,Debit item costs. Credit when invoice updating.
[/table]

The following sales/revenue accounts need to be set up:
[table]
Sales Account,Ledger,Purpose
Customer summary account,Balance,Debit on-accounts. Upon invoice updating debit the invoice amount.
Invoiced revenue,Profit&Loss,Upon invoice updating credit the sales value of all costs.
WIP Invoiced – On account,Balance,Credit on-accounts. Upon invoice updating debit on-account.
[/table]

Time and Material Project with Accruals

Set up the following ledger cost accounts to run a time and material project with
accruals:

[table]
Cost Account,Ledger,Purpose
Cost,Profit&Loss,Debit all costs
Payroll allocation,Profit&Loss,Credit hour costs
[/table]

The following sales/revenue accounts need to be set up:
[table]
Sales Account,Ledger,Purpose
Invoiced revenue,Profit&Loss,Upon invoicing credit the sales value of all costs.
WIP – Sales value,Balance,Debit fees and the sales value of costs. Credit when invoice updating.
Accrued revenue – Sales value,Profit&Loss,Credit fees and the sales value of costs. Debit when invoice updating.
WIP Invoiced – On-account,Balance,Credit on-accounts. Debit on-accounts when offsetting on the invoice update.
Customer summary account,Balance,Debit on-accounts. Upon invoice updating – debit the invoice value (sales value of all costs and fees minus on-account transactions).
[/table]

Advanced Fixed-Price Projects

Since the matching between costs and revenues in fixed-price projects is not done directly the set up is more complex. There are two methods of assessment principles used to match costs to revenues in fixed price projects:

  • Completed contract method – Revenue and costs are not recognized until the contract is completed.
  • Completed percentage method – Adds revenue and costs to profit and loss accounts as the work progresses. The revenue recognized is determined by the stage of completion of the contract every time that estimates post.

Both methods can operate in two principles:

  •  Sales value principle: Costs and revenue are matched at the end of  the project with a sales value.
  • Production and profit principle: Costs and revenue are matched with a sales value that is split into realized costs and a calculated profit.

POST_004_Assessment_principle

 Fixed-Price Project with Completed Contract

Set up the following ledger cost accounts for a fixed-price project by using the completed contract method.

[table]
Cost Account,Ledger,Purpose
Cost,Profit&Loss,Debit all hours expenses and items.
Payroll allocation,Profit&Loss or Balance,Credit hour costs.
WIP – Cost value,Balance,Debit hour and expenses.
Cost – Item,Profit&Loss,Credit item costs.
WIP – Cost value – Item,Balance,Debit item costs.
Inventory issue,Balance,Credit item costs.
Expense offset,Balance,Credit expense costs.
[/table]

Set up the following ledger sales accounts for a fixed-price project by using the
completed contract method.
[table]
Sales Account,Ledger,Purpose
Customer summary account,Balance,Debit on-account invoices.
Invoiced on-accounts,Profit&Loss or Balance, Credit on-account invoices. Debit when eliminating.
Accrued revenue sales value,Profit&Loss,Debit on-accounts. Credit when eliminating.
WIP invoiced – On-account,Balance,Credit on-account invoices. Debit when eliminating.
WIP sales value,Balance,Debit on-account invoices.Credit when elimination.
[/table]

 Fixed-Price Project with Completed Percentage

Set up the following ledger cost accounts for a fixed-price project with the
completed percentage method.

[table]
Cost account,Ledger,Purpose
Cost,Profit&Loss,Debit all costs.
Payroll allocation,Profit&Loss or Balance,Credit hour costs.
Inventory issue,Balance,Credit item costs.
Expense offsets,Balance,Credit expense costs.
Cost items,Profit&Loss,Debit item costs.
[/table]

Set up the following ledger sales accounts for a fixed-price project with the
completed percentage method.

[table]
Sales Account,Ledger,Purpose
Customer summary account,Balance,Debit on-account invoices.
Invoiced on-account,Profit&Loss or Balance,Credit on-account invoices. Debit when eliminating.
WIP invoiced – On-account,Balance,Credit on-account invoices. Debit calculated sales value when reaching completion percentage of 100.
Accrued revenue sales value,Profit&Loss,Credit the calculated sales values that are based on the completion percentage. Debit the calculated sales value when reaching the completion percentage of 100 (only when the onaccount posted to profit and loss).
WIP sales value,Balance,Debit the calculated sales values that are based on the completion percentage. Credit the calculated sales value when reaching the completion percentage of 100 and the on-account invoices are posted to profit and loss. Credit during elimination when the on-account invoices are posted to the balance account.
[/table]

Internal Projects with WIP

Set up the following ledger cost accounts for a internal projects with WIP.

[table]
Cost account,Ledger,Purpose
Cost,Profit&Loss,Debit all costs.
Payroll allocation,Profit&Loss or Balance,Credit hour costs.
WIP cost,Balance,Debit costs. Credit when moving costs to profit and loss.
Cost item,Profit&Loss,Credit item costs. Debit when moving costs to profit and loss.
WIP cost item,Balance,Debit item costs. Credit when moving costs to profit and loss.
[/table]

Investment projects

Set up the following ledger cost accounts for a investment project.

[table]
Cost account,Ledger,Purpose
Cost,Profit&Loss,Debit all costs.
Payroll allocation,Profit&Loss or Balance,Credit hour costs.
WIP cost,Balance,Debit costs.
Cost item,Profit&Loss,Credit item costs.
WIP cost items,Balance,Debit item costs.
Accrued loss,Profit&Loss,Debit costs when eliminating.
WIP accrued loss,Balance,Debit costs.
[/table]

This was just one small part of the entire setup, we’ll explore the rest in later posts.

Keep on DAXing!

Distribution List and Retail sync

Let’s look at a common scenario. You have created the database profiles and tested them successfully. You run any of the N-Jobs, the system shows “Successful with commerce data exchange.” in its Status messages in the Distribution schedule form. Still, nothing is replicated to the stores and the messaging tables at the HQ database and Store database are empty. There are also no temporary files in the “Work” directory for the Sync service on the server or store.

First thing that you should check if you attached the particular store to the distribution location list which is located in Retail -> Setup -> Retail scheduler -> Distribution location list.

Distribution_location_list

If its still not working check your current version. In AX 2012 R2 CU7 I encountered a weird “bug” in the distribution location number that might help you. If the value is not a number for example 00001 the system won’t give you an error but it won’t send any data, there will be no messages etc. I tried it on the demo VM R2 CU6 and it seems that there it’s working so it’s only an issue in CU7.

Until next time!

 

Retail statement parameters

Today we are going to talk about retail statement parameters and how they interact with each other and use transactions to create statements. Retail statements are one of the most important aspects of retail operations and a correct setup is very important for any business scenario.

Specifically, the focus will be on the parameters that split or group transactions based on properties of the transactions.

POST_002_Parameters

Retail statement parameters

The parameters explained will be the following:

  • Statement method
  • One statement per day
  • Closing method
  • End of business day
  • Post as business day

The first parameter “Statement method” is pretty straightforward. It has 4 options:

  • Staff – groups transactions on a statement by payment method and  staff
  • POS terminal – groups transactions on a statement by payment method and terminal
  • Shift – groups transactions on a statement by payment method and shift
  • Total – groups transactions on a statement by payment method

Basically it will group the transactions in the selected transactions interval inside one statement based on the selected method (Staff, POS terminal, Shift) and then by payment method. Following are some examples of statement methods.

POST_002_Statement_method_Shift

Statement method Shift

POST_002_Statement_method_Total

Statement method Total

The second parameter is my “personal favorite” called “One statement per day”. The first time I saw it based on the name and what the official documentation says “a check box that indicates that only one statement will be created each day for the retail store”, I thought great, a parameter that will automatically split all statements by day. Right? Wrong!

After some testing and not getting anywhere we of course start digging into the code to see what it does. We find this jewel in several spots which contains code for the “End of business day” parameter aswell:

if (_storeTable.oneStatementPerDay == true)
    {
        this.transFromDate = this.transToDate;

        if (retailStoreTable.stmtCalcBatchEndTime > 0)
        {
            this.transFromDate--;
            this.transFromTime = retailStoreTable.stmtCalcBatchEndTime;
        }
}

The thing that this parameter will actually do if it is set is just initialize the “From Date” on the statement. Some modifications needs to be done to be able to create statements that are automatically split by day. For example, if we wanted to group all shifts that happened in a day in a specific store we can only do it manually by the following steps:

  1. Make sure the statement method for the store is “Shift”
  2. Closing method is “Date and time”
  3. Go to Retail -> Journals -> Open statements
  4. In the group New, choose New Statement
  5. Choose the start and end date and time on the transaction interval to one day
  6. Calculate the statement

Steps 4-6 need to be repeated for every store and every day that needs to be calculated which is tedious but there is no alternative in AX. In one of the next blog posts we will make some modifications in the AX periodic job “Calculate statements” to enable and automate automatic statement creation split by day.

The closing method parameter can be a tricky one. One of the important facts to remember about this parameter is to never change it once it is set. The closing methods are:

  • Date and time
  • Shift

The “Date and Time” method will pull the transactions that are in between the specified Transaction interval on the statement. The “Shift” method works in a specific way. It will pull a whole, closed shift with unposted transactions into the statement if the interval is within the shift. An example of this is in the picture below:

POST_002_Closing_method_Shift

Closing method Shift

Here we can see that the interval is from 2/5/2014 to 2/6/2014 but the statement pulled shifts that have transactions ranging from 2/2/2014 to 2/6/2014. Now, if we used the statement method “Date and time” and posted some transactions but not the whole shift and then we decide to switch me method to “Shift” we won’t be able to pull the shift that already has posted transactions! Here’s an example:

A shift that has transactions from 4/2/2014 to 4/11/2014

POST_002_Shift_range

The shift ranging from 4/2/2014 to 4/11/2014

Some of the transactions on the shift are already posted using the “Date and Time” closing method.

POST_002_Posted_transactions_in_the_shift

Posted transactions in the shift

We try to calculate the statement but nothing is pulled into the statement because some of the transactions are already posted.

POST_002_No_shift_or_transactions_are_pulled

No transactions are pulled

The parameters “End of business day” and “Post as business day” are linked. The “Post as business day” must be checked for the system to use the time specified in “End of business day” for statement posting. In the “End of business day” field enter the time that the last transaction must be recorded to be included in the business day’s statement. The “Post as business day” parameter is needed when we want to include transactions that happened on different days on the same posted sales order invoice. There is a very good example of this on the Microsoft Technet:

“A store is open between the hours of 8:00 A.M. and 3:00 A.M., and the store is configured with the Post as business day check box selected. The store records transactions between the hours of 8:00 A.M. and midnight on May 31. The store also records transactions between the hours of 12:01 A.M. and 3:00 A.M. on June 1.

When the store posts its statement for the close of the business day, a sales order is generated that includes all transactions that were recorded between the business hours of 8:00 A.M. and 3:00 A.M., even though the transactions occurred on May 31 and June 1.

If the same store is configured with the Post as business day check box cleared, separate sales orders are generated when the store posts its statement for the close of the business day. One sales order includes the transactions that were recorded between the business hours of 8:00 A.M. and midnight on May 31, and the second sales order includes the transactions that were recorded between the hours of 12:01 A.M. and 3:00 A.M. on June 1.”

The last parameter “Split by statement method” refers to the first parameter in this “Statement/Closing” FastTab which is “Statement method”. If this parameter is checked the system will create separate statements based on the selected “Statement method”. Here is an example of this by using the “Calculate statement” job in the Periodic section of the Retail module. The selected “Statement method” is Shift.

After running the job the system made separate statements for each shift. Notice that the system calculated the statements for the whole interval up to today which means there is no start date.

POST_002_Split_statement_method_checked

Split by statement method checked

If the checkbox is not selected and we run the job again all unposted shifts are pulled in one statement.

POST_002_Split_statement_method_unchecked

Split by statement method unchecked

That’s all you need to know about retail statement parameters and transactions grouping. Keep on DAXing!

Retail Offline Sync Service fails to synchronize

Last week I had a case where my Retail Offline Sync Service stopped working.

A little background first. This is the topology for a Retail store and what this post is all about.

POST001_Retail_Sync_Topology

The POS has to be in „Work offline mode“ so that it checks its own database. If the POS is in “Online mode” it will check the store database.

POST001_Retail_Sync_Work_offline

Let’s get back to our problem. I added a new Expense account to my store expecting it will be replicated to my offline POS database.

POST001_Retail_Sync_Adding_expense

We go to our POS that is currently in offline mode and try to add an expense. To our surprise the new expense account is not present.

POST001_Retail_Sync_Expense_account_store

The first thing we check is if the expense account is even replicated to the store database. So we log into the POS that is directly connected to the store database and check expense accounts. Sure enough the account is there.

POST001_Retail_Sync_Expense_account_offline

The first conclusion is that there is a problem with the Offline Sync Service in the store. By default the tracing on the Offline Sync Service is not enabled. To enable the tracing we need to change the config file. The config file on your computer with the offline POS database is located in:

C:Program Files (x86)Microsoft Dynamics AX60Retail POSRetailOfflineMicrosoft.Dynamics.Retail.Offline.Service.exe.config

Append the next code to the config file:

</configuration>
…
  <system.diagnostics>

    <switches>
    <!--  0-off, 1-error, 2-warn, 3-info, 4-verbose. -->
      <add name="SyncTracer" value="3" />
    </switches>

    <trace autoflush="true">

      <listeners>
        <add name="Tracing" type="System.Diagnostics.TextWriterTraceListener" initializeData="SyncTrace.log" />
      </listeners>
    </trace>
  </system.diagnostics>
…
</configuration>

In the “SyncTracer” value field you can specify how much info we want from the tracer. We will put this to “info” to see a little more information. After all is done and finished don’t forget to switch the tracing to “off” because the trace file can become rather large.

After we changed the config file we restart the offline sync service just in case.

POST001_Retail_Sync_Restart_service

The log file  SyncTrace.log is created in the same folder as the config file. We open the log file and this is what we see.

ERROR , Microsoft.Dynamics.Retail.Offline.Service, 7, 04/13/2014 09:56:14:123, LocalConnectionStringMissing

It’s telling us that some connection string is missing. So we open up the config file for the offline sync service again and check the connection strings. This is what we see:

    <connectionStrings>
        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.LocalDBConnectionString"
            connectionString="" />
        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.RemoteDBConnectionString"
            connectionString="Data Source=AX62POS-ZG-01;Initial Catalog=RetailSplitStore;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True" />
    </connectionStrings>

Apparently the connection string for the offline database is missing. The connection string for the remote database (the store database) is correct. We fix the connection string and restart the service:

   <connectionStrings>
        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.LocalDBConnectionString"
            connectionString="Data Source=AX62POS-ZG-02;Initial Catalog=RetailSplitStoreT2;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True" />
        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.RemoteDBConnectionString"
            connectionString="Data Source=AX62POS-ZG-01;Initial Catalog=RetailSplitStore;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True" />
    </connectionStrings>

TIP: To check if the data is replicated we could just connect to the databases and check the tables. Sometimes it’s a much faster way to check replication instead of going through the POS. This is the store database data in the RETAILINCOMEEXPENSEACCOUNTTABLE table (the one with the income and expense accounts). We can see that our new expense account is present:

POST001_Retail_Sync_Store_db

This is the offline POS database data for the same table:

POST001_Retail_Sync_Offline_db

The account is still missing. We open up the SyncTrace.log again and see this:

INFO, Microsoft.Dynamics.Retail.Offline.Service, 7, 04/13/2014 10:04:53:132, Terminal Offline: YES

The catch is that it won’t sync the database unless we get the POS back into online mode! Once we do that the account is replicated to the offline database.

POST001_Retail_Sync_Offline_db_ok

We take the POS offline again and try to add an expense account and sure enough it’s there:

POST001_Retail_Sync_Offline_expense_ok

I hope you enjoyed the tips&tricks of the offline sync service. Until next time!