Strip Master Data from Channel DB

There is a nifty feature on the Retail side which will perform a clean up for some of the global data to get the Channel DB lean. This is especially important in cases where the POS itself acts as an RSSU and you would look at every opportunity to get the irrelevant data in the Channel DB to disappear(purged). 

The feature is available in the Retail scheduler parameters form under the Post database sync, make sure to enable the field Clean up irrelevant master data as True.

What it essentially does is it would execute a stored procedure on the Channel DB everytime Async client has Applied the download sessions. The entities involved that would be cleared are Customers, Employees, Product, Loyalty and Address. The Products and Customers would ideally be already handled from Assortments and Customer address book respectively. There is a possibility of issues arising if you are using Loyalty and in those cases it is better to disable this feature. Please do use the features with caution incase some complicated joins used by CRT may not work due to missing data. 

D365 for Retail - On Premise is on GA mode

Finally, the final version for the Retail on premises which released this November for D365 F&O is out as a seperate line of Products for the Retails as D365 for Retail. For the time being both the on premises version will be available as a separate deployments and the main reasons being from the licensing perspective for Retailers. The D365 for Retail product is fully upgradable to the D365 F&O if required at a later stage as the core product versions will still be maintained at the same level during the version releases.

I've consolidate the complete list of URL's that are beneficial to take a look for the on-prem release

1. On-premises Deployment Page - https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/deployment/on-premises-deployment-landing-page
2. Retail capabilities available for On-Prem - https://docs.microsoft.com/en-us/dynamics365/unified-operations/retail/retail-onprem   
   Note: Hardware station and Price simulator is currently listed as Not available, hope it is corrected soon
3. Deployment Steps for On-Prem - https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/deployment/setup-deploy-on-premises-pu12
4. Retail Components Installation - https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/deployment/deploy-retail-onprem
5. Retail POS API's - https://docs.microsoft.com/en-us/dynamics365/unified-operations/retail/dev-itpro/pos-apis
6. What's new or changed? - https://docs.microsoft.com/en-us/dynamics365/unified-operations/fin-and-ops/get-started/whats-new-changed
7. On-Prem Lifecycle Policy & Version Numbers - https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/migration-upgrade/on-prem-version-update-policy

Disclaimer: Please note due to the continuous updates to the Product & docs.microsoft.com some URL's may be altered, moved, edited or removed. All information is correct as of 09/11/2018.

My take on the Data migration

Data is the key to a successful implementation which can make the implementation as painless as possible for Customer to transition from their legacy systems to the new Dynamics AX application. The data gives them an unparalleled familiarity to use and embrace the new application even though they are in the midst of transition. The end users have nothing more joy in viewing the results in Reports or Inquiries with their data when they achieve their objective using AX Business Process. So Data Migration phase is an important area which needs to be planned and analyzed with importance right from the requirements stages with just the level of priority as the business processes.

Data migration requirements planning allows the Consultant to estimate and analyze the data maintained or archived in the legacy system. The data analysis estimation involves both size and quality of data that can be migrated to AX. Most importantly estimate the efforts required if there are complications and can be flagged early to the Customer. There are possibilities to pick up some functionality that may be required based on the data available in legacy but which has not been discussed or identified during the requirements workshops. Overall the effective Data migration strategy is crucial for getting the application ready for Training and UAT milestones. This in turn is the precursor signoffs for the Production cutover finalization and rollouts.

The workshops for data migration will involve all the key users spanning across modules but related business processes to ensure smooth decision making with consensus and settle any clarifications that may arise immediately. The workshops also allows the Consultant to identify who owns the data. For the session to be effective, key users may share their module data by showcasing reports, screenshots, etc ahead so Consultant can do a simple analysis and prepare any questions ahead of the data migration workshops. Based on the business requirement sessions conducted earlier, the Consultant should be able to identify and narrow down on the entities to be migrated and classify them as Configuration setup (Ledger accounts, GST, Payment terms, Module Parameters, etc), Master data (Customers, Vendors, Products, etc), Orders (Sales quotations, SO, PO etc) and Opening balances (FA, Ledger, Inventory, Cust/Vend, etc). Few more workshop sessions can be conducted in this format to ensure buy in from all the stakeholders and finalize the templates along with the conversion values required for AX.

Customers IT team or the key users will normally prepare a first round extraction as per the template provided for the entity along with the conversion values provided by the Consultant. There may be more extractions based on the feedback from consultant and the data migration templates will locked. Around the same time more planning will be done for firming up the dates for the real extraction for UAT based on the project schedule, considering if there are any other developments that is required for the data entity import. Generally an accepted 2 rounds of import is done to ensure the efficacy of the data and estimate timelines for the actual migration in production.

To summarize the data migration strategy, you would need to do;

Objectives

• Identify What to migrate, Source of data and How to migrate
• Classify and communicate clearly so there is no misunderstandings
• Ensure what’s in scope and out of scope

Stakeholders

• Key users who own their respective module data
• IT team who deliver the data as per template agreed
• Consultant who plans from strategy to execution
• Anyone else who is part of Sign off

Templates or Scripts

• Proper definition of what qualifies as a clean data expected for AX
• Columns with Legacy data conversion requirements to AX data
• Sign off both Migration strategy and Templates/Scripts

Simulate

• Ensure at least two rounds of testing including with Customizations, if any
• Sign off to ensure data imported is as per expected format

The key ingredient for a successful and a smooth migration is adequate planning, proper understanding, and do it early! Now that we have got the strategy part over (my understanding J), let’s do some import to AX 7 using Data Entities!!!

Disclaimer: Views expressed are entirely my own! please follow rigorous processes and project methodology for Actual Customer Implementations.

Dynamics AX 7 RTW and Activation of Cloud POS

Dynamics community is abuzz now after the Virtual launch of the Dynamics AX RTW which was promoted to GA. You should be able to deploy your demo, production or dev/test environments from LCS. A lot has been written over the importance of LCS and cloud deployments, so am not going to waste time on them but going to dig right in with the deployment of Cloud POS from RTW version. There are some pre-requisites that need to be done to ensure the Cloud POS is up and running in no time.

• Dynamics AX Batch management service

Dynamics AX Batch management service is responsible for executing all the batch jobs in AX. Consider it as a legacy [limited] AOS service which is stripped of all the rich client UX elements with just the business logic execution component. It is set as Automatic by default but sometimes I've found it doesn't start properly. Push it to Delayed Automatic to ensure it starts after the Server has completely booted up. You can find it under the Services, 

• Employee AAD entity

AX user is automatically associated with the AAD identity(office 365 poral user id aka @onmicrosoft.com account) but you would need to do a double check for the Workers to ensure the correct AAD entity is associated properly. Find out the Employee name from the User id and then go to the Employee record, link it from the Retail ribbon bar > External identity section > Associate existing identity,

• Employee address book

Add a proper addressbook for the Employee, this is crucial to ensure the Cloud POS activation wizard will allow you to choose the store. I choose everything just in case;

• Employee Job

Employee Julia's job scope in Marketing Executive was not part of retail, so had to choose the POS permission group as Manager to let her activate and use the Retail Cloud POS,

• Screen layout

The RTW that I deployed did not have a Cloud POS screen layout profile for Houston store, so had to link one to the Store or Employee. So make sure you save the hassle by checking the profile to the Store,

• Distribution schedule - Staff

Push it to the Channels, don't wonder if you don't find a separate Channel DB on the RTW demo. It is a topic for a separate post by itself. Run the Distribution Schedule for Staff. 

Activation of Cloud POS

Cloud POS activation is a breeze now in RTW, with the introduction of a wizard it makes it as painless as possible for the Customers and Partners. Go to the LCS, you should be able to see the Cloud POS url or if you are in server you can access the Cloud POS from IIS manager to view the URL. The first step would be to provide the Retail Server URL to the activation wizard. You can get it from the channel profile settings too.

Click Next and it will bring out all the stores that is accessible for the Employee,

Choose the Store and click Next, in my case I used Houston,

The wizard will pick up all the Registers with their Device activation status, go ahead and pick one. I chose Houston-14

Let the wizard cook up some magic by completing the 10 step which includes creating device token, gathering device configurations, hardware profiles, number sequences, etc

Viola, you get the message as Device successfully activated and Cloud POS ready to log in. Use Julia's employee id as 000020 and password as 123

Play with your cloud POS!

Life Cycle Services(LCS) and Deployment of AX 7 instance

As by now everyone should be aware that the direction for manageability of AX services will be handled from LCS (Life Cycle Services), in fact you can maintain your complete project repository including the infrastructure details from within the LCS. To the extent you can even raise a support request and Microsoft support will be able to effectively collaborate with the Partners and Customers to resolve the issues. Microsoft has done a significant amount of investment in this area and is now started reaping the rewards as more and more partners are engaging LCS for the Project activities.

  

LCS Project tools with cloud environments

I recently deployed a CTP8 demo instance via LCS and they have made the user interface so friendly that you can get your demo instance up and running on Azure in just a couple of hours. You will know the deployment is complete once the LCS status changes from Deploying to Deployed. At this moment you will be able to view all the services of the Demo instance, like the access URL for AX 7, Cloud POS, Storefront site and AX 7 trial experience page. The trial experience is basically a guided walkthrough for new AX 7 users on the different UX features introduced in the new version.

AX7CTP8 Cloud instance deployed

Scroll further down on the LCS page, you should be able to see all the user accounts created and deployed by LCS for the services. Click on the Login to AX7 using the office 365 account and you are good to go start browsing and accessing the AX 7 functionalities from the web. If it is your first time logging in to AX 7, just remember to use the trail experience to understand the Module menu’s and search functions. Finally start exploring, my next post will focus on the Retail part where I’ll be deploying a Cloud POS device. Stay tuned!

New beginning - Microsoft Dynamics AX 7 Reloaded

Dynamics AX 7 public preview has already been launched recently and right now all Partners and Customers should be able to start getting the feel of the next generation cloud powered ERP. The direction from Microsoft after the leadership change has been quite apparent, that there is going to be a very VERY BIG focus on the cloud aka Azure based services and applications. And the apparent change in removal of the rich client is a game changer in the ERP. Everything now runs on the internet explorer(or Chrome if you like it)! Hell yeah, now the sales team has much more to wow the customer about the ease of web use and truly a cross platform UX experience. The AX 7 ERP solution should be able to cater to a varied and most industries from Finance to Manufacturing , Supply chain to Retail and almost every other industry that is in between or probably even a hybrid.
Everyone goes gaga on the cloud, what is it all about? What this means for the customers and partners is, they can start deploying the environments on Azure and this completely eliminates the need for a disaster recovery solution. Microsoft will be responsible for the data centers and customers can have high availability service agreements depending on the business continuity recovery times.
Will Microsoft not release an On Premise version of AX 7? Nope, it means Microsoft just wants to prioritize in getting it right for the cloud customers first before the launch of next release which will be On Premise version. Another factor that's tied to the release of an on premise version is the general availability of Windows server 2016 which has native features on ADFS(Active Directory Federation Services) which simply means that you can use this component to authenticate non active directory users within the server. The current Windows Server will support if you download an add-on for ADFS to enable this feature.
There is a mammoth of goodies on the features for the new release, I am not sure where to start but some of them are HTML5 based UX, LCS manageability, cross platform support on web/mobile, full visual studio integration and many more.
General availability of AX 7 is still in the Q1 2016 and going by the momentum it should be out in a a few more weeks, The current version which the public release is made for the partners and customers is based on the CTP 8 which has the version(7.0.1186.16043)


PS: All versions indicated are correct at the time of publishing of post, Microsoft may change version numbers as and when kernel updates are made available or after GA.

Dynamics AX 7 expected to be released in Q1 2016

Microsoft has announced that the next version of the AX 7, which is completely hosted as a web application is proposed to be released in first quarter of 2016. The best news yet that has been from the Dynamics AX release team is it would be available for public preview exclusively for Partners and Customers from early December 2015.
Checkout the press release reports to get to know more about what coming in the next release!

Dynamics AX 2012 R3 CU10 released

The latest cumulative update of hotfixes for the AX 2012 R3 has been released from LCS, the original release post from the Dynamics AX in Market Engineering team can be found here. The latest build has been updated to 6.3.3000.110 but if you use slip stream option for a full installation the build number will be shown as 6.3.3000.111.
Coming back to the post what's updated in the latest hotfix basket in Retail, probably the most important is the POS performance. The biggest strength in the Retail POS is its ability to automatically calculate the best discount that can be applied to the transaction based on the products/store/customer/period combinations. But it turns out to be the biggest performance backdrop if the discounts/products increases more than tens of thousands of records. It seems there has been some issues addressed for these scenarios by adding a maximum number of steps/loops before the best price has been picked up for the product within the shopping cart. And this has been implemented as a configuration in the Retail parameters. The functionality should have come long time back, even though too late but better now than never.

Retail Time Clock Entries

If you have been using the Retail Module, you might have noticed there are couple of operations called Time Clock and View Time Clock.

The time clock operations enable the Store to capture the Cashier/Supervisors working shift schedule by capturing their clock in/out information. The functionalities enables HQ to capture the exact times that a cashier has come for work, duration of breaks and when he signed off for the day. This is not linked to the Login/Log off details of the cashiers but built as a decoupled functionality to store the time registrations for employees.
The huge potential that we can see it is fully integrated with the Retail and HR module, enabling you to generate working time reports or send this data to payroll systems to process the cashiers salary. Please do note the operations performed in the Time clock entries are all real time meaning the Terminal should be fully online and active connection available to make the time clock entries. Before you can start using the Time registration for Employees, make sure to activate Time registration in terminals for those employees.
After you have made some Clock in/Break For Lunch/Clock out, you can immediately start seeing the Time registration transactions under the Worker > Time Registration Tab > Journal Registrations.

Azure Marketplace - Evaluation version for Dynamics AX 2012 R3

Life Cycle Services has just announced in their July 2015 Release that they are releasing the Evaluation version of AX for all Customers and Partners. This follows a similar concept of Dynamics CRM when they offered a 30 day trials for anyone who wanted to try the CRM online. 

The details that you would need is a valid LCS account and Azure portal access, some of the other details that you need to select/check are Location, Sizing, LCS project and T&C's. Currently you will only be able to deploy the AX 2012 R3 version, and it aptly seems to pave the way for the AX 7.0 version which is supposed to be fully web based client. You can access the Market place for Dynamics AX Evaluation copy from this link. Have fun with the evaluation versions and make sure your Azure subscriptions don't burn up too quickly by turning off the VM's when not in use.

Easily move Retail Till Layouts between environments

Many of the times we would like to see the Till layout in our Live environment on our Testing instance. The usual way would be to replicate manually the changes on the Till layout designer one by one or move the complete transaction DB from Live to Testing instance. Both of which will give a lot of downtime or a bit cumbersome! A easy hack to replicate the details would be to just copy over all the relevant tables via SQL insert. I had went through the table relationships and have compiled the necessary ones which you can use for the copy functionality. The data for design is predominantly saved as XML data which is used by the .NET runtime on POS for building the layout or an activeX component for designer in AX which allows for layout changes. At no point you should manipulate the XML data directly as it can make the layout to crash, we need to allow the ActiveX designer to generate the XML data, that's one of the reasons you need to provide additional Read/Write permissions for AX database for the users who manage the Till Layout designs.
The list of tables that are involved in the Till Layouts are,

• RETAILBUTTONGRID
• RETAILBUTTONGRIDBUTTONS
• RETAILIMAGES
• RETAILTILLLAYOUT
• RETAILVISUALPROFILE
• RetailDeviceTypes
• RetailPOSTheme
• RetailTillLayoutZone
• RetailTillLayoutZoneReference

AX 2012 R2 has the first 5 tables as the remaining tables were included in R3 with the introduction of MPOS. And here is the sample SQL Select into statement, please make sure you've moved the records in Testing table to a backup table before executing the statement.

select * into [--TestInstance--].[dbo].[RETAILBUTTONGRID]

 from [--LiveInstance--].[dbo].[RETAILBUTTONGRID] 

Kindly test carefully and evaluate relationships of the tables to be sure there has not been any changes incorporated by Microsoft in version or hotfix changes.

Dynamics AX 2012 R3 CU9 Released

AX 2012 R3 CU9 is officially released with the KB3063879. The release has been positioned only for AX 2012 R3 hence it seems the previous releases for R1 and R2 will need to wait a bit longer. An attractive option for the CU9 release is if the existing environment is linked to LCS they you may upgrade it directly from LCS website. Here is the link to the actual release which has all the information and notes related to Installation guides and change history.

MB6-701 Dynamics AX 2012 Retail Certification successfully cleared

Hurray! I've managed to complete the AX 2012 Retail certification yesterday. During registrations probably first thing you will notice is Microsoft will direct us to Pearson VUE instead of Prometric. I wanted to grab the second shot offer before it ends this month end so I quickly scheduled it for this week based on the online slots available. 

The exam duration is 1.5 hours for 50 questions and the testing UI felt more like a windows app. Most of the questions are modeled based on how we apply the retail concepts and configuration setups rather than direct question and answers. If you have enough experience configuring the retail components this should not be an issue. Using the elimination rule for the options will come in handy to arrive at the correct answer. For the time being am just glad I didn't need to use the second shot offer!

Retail Transaction Service Connection - Error 13010 Could not connect to the Transaction Service

The retail transaction service on the POS can get cranky at times by not being able to reach the backoffice HQ to do a specific operation. The error message for 13010 is very generic meaning there is a connection issue between POS client and Retail Transaction service. More indepth details can be found from the Event viewer log where the error arises. The current issue in discussion happened when there was a sudden failure due to Business connector for Transaction service was unable to access Dynamics AX.

There are a few possible causes for the error could be due to permissions and rights issue for the business connector service account which might have been modified by the system administrator. If that's not the case and you are sure that the service account was not changed recently then it is recommended to Reset the IIS. The IIS reset will refresh the security tokens that might have linked to the service accounts. The business connector in my case was able to successfully connect back to AX after the IIS reset and Transaction service was back to normal.


Detailed event viewer entries
1)
LSRetailPosis.TransactionServices.InvokeMethod: System.ServiceModel.Security.MessageSecurityException: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail. ---> System.ServiceModel.FaultException: An error occurred when verifying security for the message.
   --- End of inner exception stack trace ---

Server stack trace:
   at System.ServiceModel.Channels.SecurityChannelFactory`1.SecurityRequestChannel.ProcessReply(Message reply, SecurityProtocolCorrelationState correlationState, TimeSpan timeout)
   at System.ServiceModel.Channels.SecurityChannelFactory`1.SecurityRequestChannel.Request(Message message, TimeSpan timeout)
   at System.ServiceModel.Security.SecuritySessionSecurityTokenProvider.DoOperation(SecuritySessionOperation operation, EndpointAddress target, Uri via, SecurityToken currentToken, TimeSpan timeout)
   at System.ServiceModel.Security.SecuritySessionSecurityTokenProvider.GetTokenCore(TimeSpan timeout)
   at System.IdentityModel.Selectors.SecurityTokenProvider.GetToken(TimeSpan timeout)
   at System.ServiceModel.Security.SecuritySessionClientSettings`1.ClientSecuritySessionChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ReliableChannelBinder`1.ChannelSynchronizer.SyncWaiter.TryGetChannel()
   at System.ServiceModel.Channels.ReliableChannelBinder`1.ChannelSynchronizer.SyncWaiter.TryWait(TChannel& channel)
   at System.ServiceModel.Channels.ReliableChannelBinder`1.ChannelSynchronizer.TryGetChannel(Boolean canGetChannel, Boolean canCauseFault, TimeSpan timeout, MaskingMode maskingMode, TChannel& channel)
   at System.ServiceModel.Channels.ReliableChannelBinder`1.Send(Message message, TimeSpan timeout, MaskingMode maskingMode)
   at System.ServiceModel.Channels.SendReceiveReliableRequestor.OnRequest(Message request, TimeSpan timeout, Boolean last)
   at System.ServiceModel.Channels.ReliableRequestor.Request(TimeSpan timeout)
   at System.ServiceModel.Channels.ClientReliableSession.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ClientReliableDuplexSessionChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
   at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.CallOpenOnce.System.ServiceModel.Channels.ServiceChannel.ICallOnce.Call(ServiceChannel channel, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.CallOnceManager.CallOnce(TimeSpan timeout, CallOnceManager cascade)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.Dynamics.Retail.TransactionServices.ClientProxy.ITransactionService.InvokeMethod(RequestInfo requestInfo, String methodName, Object[] parameters)
   at LSRetailPosis.TransactionServices.InvokeMethod(String methodName, Object[] parameters).


2)
BC.Net connectivity failure.AXNET.Logon() method called with Company: null | Language: null | ObjectServer:

   at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(String company, String language, String objectServer, String configuration)
   at Microsoft.Dynamics.Retail.TransactionServices.AxaptaWrapper.CreateAxapta(RequestInfo info)

3)
Axapta.Logon() failed. Exception details: Microsoft.Dynamics.AX.ManagedInterop.NoSecurityKeyException
   at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(String company, String language, String objectServer, String configuration)
   at Microsoft.Dynamics.Retail.TransactionServices.AxaptaWrapper.CreateAxapta(RequestInfo info)

Retail Transaction Service Error for NET.TCP

There can be situations when the real time service for a store suddenly stops working if it uses the net.tcp protocol to communicate with back office AX. You will be provided with a connection unsuccessful exception from the server due to time out. And a generic error during an operation For Eg:- Recall Sales order operation from the POS as "An error occurred while refreshing the list". A closer look at the event POS will provide insights into the net.tcp error log like below.

Microsoft.Dynamics.Retail.Pos.SalesOrder.WinFormsTouch.frmGetSalesOrder, Text: Sales orders: LSRetailPosis.PosisException ---> System.ServiceModel.EndpointNotFoundException: Could not connect to net.tcp://server.ttprod.local:8190/RealTimeService/Service.svc/Common. The connection attempt lasted for a time span of 00:00:01.0312456. TCP error code 10061: No connection could be made because the target machine actively refused it 172.xx.xx.xxx:8190.  ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 172.xx.xx.xxx:8190

   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)

   at System.Net.Sockets.Socket.Connect(EndPoint remoteEP)

   at System.ServiceModel.Channels.SocketConnectionInitiator.Connect(Uri uri, TimeSpan timeout)

   --- End of inner exception stack trace ---

The server had actively refused the connection for net.tcp connection as the listener port was not started for the service. The listener Port for the network adapter can be activated on the server from the Services > NET.TCP Listener Adapter and starting the service.

Retail - Process upload jobs encountered error

You might encounter an conversion error when the Async client tries to create an upload job from the Retail transactions, more specifically it is a decimal to integer SQL conversion cast error. The error will cause the Async client to throw an overflow exception and fall face down on the belly everytime "that" retail transaction is processed for upload package. How do you resolve it and why it occurred in the first place?

Am going to be frank, in production you will get an heart attack when you see this error on the event log when the trickle transactions are not coming back, there is no hint of why or how it happens. So put on your sherlock holmes hat and start your investigation for some clues to solve this error.

First lets see the Why part, the Async client raises the exception when it tries to create a package for one of the columns in the upload tables which has a values larger than what an integer can hold. Yes it can happen if the cashier accidentally scans a 13 digit EAN 13 barcode to a Quantity or Price field. The scanners at the Hypermart cashiers are always ON meaning while tendering if the product barcode could get scanned. Remember the scanner is programmed to add line feed <Enter> there by committing the transaction. So if it was at cash tender for 10$, and a barcode does gets scanned then the system will take 888,345,782,1090 and return the Change back as 888,345,782,1080. No issues now as the RetailTransactionPaymentTrans table record is created with this gigantic value and sale gets completed. The real exception comes when the Pull jobs starts to create records for this transaction. And the upload job will not proceed after this transaction and there by no transactions will trickle feed back to HQ.

Simple script to identify the issue by Qty or Amount

Select * from RetailTransactionSalesTrans where Qty < -50000

Select * from RetailTransactionSalesTrans where NetAmount < -500000

How do you solve this then, if it is a test environment just truncate all the transaction tables and you would be good to go immediately. The async client springs back to action and will start churning out the upload job packages. But for those unfortunate ones when the issue occurs in production you would have to wait and find the transaction and then cleanse it before the async client can limp back again. 

A clearer understanding of the exception is more important on how it can be handled, the error is triggered by the SQL during the CAST of a column value which is in Decimal to Integer which exceeds either +/-7922816251426. Yes, thats all! Async client never tells you which transaction, value, column or even table during the exception. So you need figure it out based on the exception when it started, last complete Retail Transaction that was synced and which store the exception occurs.

Based on my experience I went through, the last perfect sync of Retail Sales was at 10:20 PM, the error started occurring at 10:40 PM for that store async client. I took a list of all the tables handled in upload job and compared one by one if they had any columns which had disproportionately high values. Below is the complete list for R3 RTM version:-

RetailFiscalDocumentModel2_BR

RetailFiscalDocumentModel2Line_BR

RetailFiscalDocumentModel2TaxTrans_BR

RetailFiscalPrinter_BR

RetailFiscalPrinterReport_BR

RetailFiscalReceipt_BR

RetailFiscalReceiptLine_BR

RetailFiscalReceiptTaxTrans_BR

RetailListingStatusLog

RetailPosBatchAccountTrans

RetailPosBatchTable

RetailPosBatchTenderTrans

RetailTransactionAddressTrans

RetailTransactionAffiliationTrans

RetailTransactionAttributeTrans

RetailTransactionBankedTenderTrans

RetailTransactionDiscountTrans

RetailTransactionIncomeExpenseTrans

RetailTransactionInfocodeTrans

RetailTransactionKitsDisassemblyTrans

RetailTransactionLoyaltyRewardPointTrans

RetailTransactionMarkupTrans

RetailTransactionOrderInvoiceTrans

RetailTransactionPaymentTrans

RetailTransactionPaymentTrans_BR

RetailTransactionSafeTenderTrans

RetailTransactionSalesTrans

RetailTransactionTable

RetailTransactionTable_RU

RetailTransactionTaxTrans

RetailTransactionTenderDeclarationTrans

RetailZReport_BR

RetailZReportTotalizer_BR

Most are useless tables which contain little to no data, just cross of them. The major tables of interest for you should be those that start with RetailTransactionxxxxx and RetailPOSBatchxxx tables. I started narrowing down all the transactions after 10:20 PM but before the 10:40 for these tables.

Try to do a SQL Cast to simulate how the async client goes belly up, for suspected columns. After a through investigation the RetailPOSBatchxxxx were pretty much harmless meaning even if it has a some large values they are handled properly in the code and data seems to flow back to AX. Then shifted my real focus to transaction tables, use a stored procedure to type cast and simulate faster for all the records which have values larger than 5 digits for either Qty or Amount columns.

Sample Scripts for RetailPOSBatchTenderTrans Table

Simple script

Select CAST(TENDEREDAMOUNT as int),cast(TENDEREDAMOUNTCUR AS int),* FROM RETAILPOSBATCHTENDERTRANS 

Detailed script for analyzing line by line

Declare @v_TRANSACTIONID nvarchar(44), @v_TERMINAL nvarchar(10), @i int,@v_date Datetime, @v_AmountTen as int,

@v_TransTime as  int,@v_BATCHID as int,@v_TENDEREDAMOUNT as decimal,@v_TENDEREDAMOUNTCUR as decimal,

@v_int1 as int, @v_Int2 as int 

Declare cur_temp cursor for

Select TOP 200 TERMINALID,BATCHID,TENDEREDAMOUNT,TENDEREDAMOUNTCUR FROM RetailPosBatchTenderTrans 

OPEN cur_temp   

FETCH NEXT FROM cur_temp INTO  @v_TERMINAL,@v_BATCHID,@v_TENDEREDAMOUNT,@v_TENDEREDAMOUNTCUR

Set @i = 0

WHILE @@FETCH_STATUS = 0   

BEGIN   

Print '@v_TERMINAL    ' + CAST(@v_TERMINAL AS varchar(1000))

Print '@@v_BATCHID    ' + CAST(@v_BATCHID AS varchar(1000))

SET @v_int1 = CAST(@v_TENDEREDAMOUNT as int)

Print '@v_int1    ' + CAST(@v_int1 AS varchar(1000))

SET @v_int2 = CAST(@v_TENDEREDAMOUNTCUR as int)

Print '@@v_TENDEREDAMOUNTCUR    ' + CAST(@v_int2 AS varchar(1000))

set @i = @i + 1

print '-----completed--- i = ' + CAST(@i as varchar(10))

print '---------------------------------'

     FETCH NEXT FROM cur_temp INTO  @v_TERMINAL,@v_BATCHID,@v_TENDEREDAMOUNT,@v_TENDEREDAMOUNTCUR

END   

CLOSE cur_temp   

DEALLOCATE cur_temp 

Repeat the steps for each of the table in the RetailTransactionTable series.

Finally found that a CAST on AmountMST columns value in RetailTransactionPaymentTrans triggers the arithmetic conversion overflow exception, exactly the one I am looking for from the Async client. The transaction seems to have been exceptionally higher than the one which can be held by an integer.

There the error popped out, got a quick approval for amending the payment amount and change back transactions from the Client to modify on Production store database. Make sure to make the value come down to acceptable range for an integer, preferable to provide a realistic value like $10 for a sales transaction that was for $7.20.

Perform the steps -> Stop the async client, commit the new values in affected transaction(multiple columns might be present in same table like PaymentAmount, AmountMST, AmountCur, etc change all to new value) and start again the async client. If you had identified the column and value correctly the upload job should start kicking again with packages, else if you get the error. Make sure to double check the same columns again if there is still any other columns that has the value. By and large the issue should have solved after you patch the transaction data.

How to avoid this scenario entirely even before it happens? Yes possible by adding some validations for Quantity and Price. Example, Make sure to always set a maximum tender amount for each tender type, for me $100,000 is a wishful figure and there would not be sale with 1 tender type with more than $100,000. So in this case even if the barcode got scanned, the amount validation will stop the payment from being completed.

For those who managed to solve please do share in the comments your approach that helped you, it could really help someone to solve the issue on production environment.

Reference Detailed Exception Log:

Process upload jobs encountered Exception. Error Details: System.OverflowException: Conversion overflows.

   at System.Data.SqlClient.SqlBuffer.get_Decimal()

   at System.Data.SqlClient.SqlBuffer.get_Value()

   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)

   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlReadRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)

   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlSourceRequestHandler.ProcessReadRequests(SqlConnection connection, SqlTransaction transaction)

   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlSourceRequestHandler.ProcessSourceRequestHeader(ISCSourceRequestHeader sourceRequestHeader)

   at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.PackRequestResultsToFile(SessionManager sessionMgr, ISCSourceRequestHeader header, String fileName)

   at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.ProcessUploadJob(Job activeJob)System.OverflowException: Conversion overflows.

   at System.Data.SqlClient.SqlBuffer.get_Decimal()

   at System.Data.SqlClient.SqlBuffer.get_Value()

   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)

   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlReadRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)

   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlSourceRequestHandler.ProcessReadRequests(SqlConnection connection, SqlTransaction transaction)

   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlSourceRequestHandler.ProcessSourceRequestHeader(ISCSourceRequestHeader sourceRequestHeader)

   at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.PackRequestResultsToFile(SessionManager sessionMgr, ISCSourceRequestHeader header, String fileName)

   at Microsoft.Dynamics.Retail.SynchClient.Core.UploadAgent.ProcessUploadJob(Job activeJob)

Job to create a new Invent Dim

Occassionally you may need a code snippet to create invent dim if it didn't exist for the inventory transactions for the new combination. Just sharing to keep it as a knowledge base.

static void CreateInventDim(Args _args) //Create a new invent Dim
{
    InventDim   inventDim,inventDimFind;

    //Batch = 123459
    inventDim.clear();
    inventDim.inventLocationId = 'HOUSTON';
    inventDim.InventSiteId = 'CENTRAL';
    //inventDim.inventBatchId = “123459
    inventDimFind = inventDim::findOrCreate(InventDim);
    info(inventDimFind.inventDimId);

}

Dynamics AX Retail POS - Inventory lookup

We can view the stock levels of the Products directly from the Retail POS using the Inventory Lookup function. The function is applicable only for stock items and is a quick & easy way to check back the stock real time at Back office. To successfully view the stock levels make sure the following setups are configured correctly for the Store.

1. Store inventory lookup option is checked
2. Retail operating unit of the Store has a valid address 
3. Atleast 1 Store locator group is defined
4. Store is successfully published
5. Product is linked to the assortment
6. Product has a Physical Stock in On-Hand inventory
7. Legal entity has Language and Address defined

To aid in additional checks, make sure the crt.Store view in the Retail Channel DB is able to show the Store correctly. If any you can view the joins to know all the related tables have been properly populated with data.

Retail POS Amount Decimal Places

Issue

I recently came across a scenario where the Retail POS suddenly started showing more than 2 decimal places. There are no changes in the Store or Retail configurations which could have triggered the sudden 3 decimal places to appear at the POS for the Amount fields. 

Resolution

I was aware that the currency plays an important part in the decimal and rounding places. After a while found that the Currency form's Rounding Rules, Prices field with 4 decimal places when changed will affect the prices in Retail POS. So after I reset it back to 0.0000 instead of 0.0010, the issue went away. After the change make sure to run the Currency job to sync the changes to POS, log off and log back in to see the new changes. The rounding should now have come back to 2 decimal places.

Retail POS does not show Price - No Channel Configuration was found

Issue: 

Dynamics AX Retail POS does not show Price for products added to sale. Also you will find an event log error as 'No Channel Configuration was found' with some stack trace.

Resolution:

The Retail POS is not able to find the Channel i.e Store information based on the data setup for the legal entity, a simple overlook on a few Legal entity setups can cause this issue and become an headache during the implementation. 

Dynamics AX Retail POS uses the Channel DB's CHANNELCONFIGURATIONVIEW to get the Channel.

If you can observe the joins in the View, it seems a simple assortment, price group and store setup is not sufficient to get the prices onto the Retail POS, you would need to setup the Legal entity properly.

To make it short, make sure you have entered the language, address in Legal entity and Retail Parameters defined. After iterating changes in the setup, run the view again to check if the Store channel is retrieved correctly in the query window. Then run the POS, with the View returning the relevant record the Retail POS should be able to compute the Product prices correctly using the Retail Pricing DLL's. You should now be able to make the product sale.

Actual View(For troubleshooting):

ALTER VIEW [crt].[CHANNELCONFIGURATIONVIEW] AS

(

SELECT

[rct].[RECID],

[rct].[CHANNELTYPE],

[rct].[INVENTLOCATION],

[rct].[INVENTLOCATIONDATAAREAID],

[rct].[CURRENCY],

[rct].[PRICEINCLUDESSALESTAX],

[l].ACCOUNTINGCURRENCY AS 'COMPANYCURRENCY',

[lpa].[COUNTRYREGIONID],

[lacr].[ISOCODE] AS 'COUNTRYREGIONISOCODE',

[rsp].[EXCHANGERATETYPE],

[rsp].[STAFFPASSWORDHASH],

[rsp].[TSPASSWORDENCRYPTION],

[rsp].[DEVICETOKENALGORITHM],

[rsp].[EARNLOYALTYOFFLINE],

[rtsp].[PROFILEID] AS 'TSPROFILEID',

[rtsp].[LANGUAGE] AS 'TSLANGUAGEID',

[rtsp].[TSSTAFF] AS 'TSSTAFFLOGONCONFIGURATION',

[rtsp].[CENTRALTABLESERVER] AS 'TSHOST',

[rtsp].[CENTRALTABLESERVERPORT] AS 'TSPORT',

[rtsp].[RETAILTSDATA] AS 'TSENCRYPTEDPASSWORD',

[rtsp].[SERVERCERTIFICATEDNS] AS 'TSSERVERCERTIFICATEDNS',

[rtsp].[SERVERSERVICENAME] AS 'TSSERVICENAME',

[rtsp].[PROTOCOL] AS 'TSPROTOCOL',

[rtsp].[SECURITYOFF] AS 'TSSECURITYOFF',

[rp].PICKUPDELIVERYMODECODE,

[rp].ELECTRONICDELIVERYMODECODE,

[rp].GIFTCARDITEM,

[rp].CANCELLATIONCHARGE,

[rp].CANCELLATIONCHARGECODE,

[rp].EXPIRATIONDATE AS 'QUOTEEXPIRATIONDAYS',

[rp].MINIMUMDEPOSITFORSALESORDER,

[rp].SHIPPINGCHARGECODE,

[rct].CHANNELTIMEZONEINFOID AS 'TIMEZONEINFOID',

[clv].LANGUAGEID AS 'DEFAULTLANGUAGEID',

[rsbp].[APIKEY] AS 'BINGAPIKEY',

[lpa].VALIDFROM,

[lpa].VALIDTO,

[crt].GETCATALOGIMAGEATTRIBUTEDEFAULTVALUE() AS CATALOGDEFAULTIMAGETEMPLATE,

[crt].GETEMPLOYEEIMAGEATTRIBUTEDEFAULTVALUE() AS EMPLOYEEDEFAULTIMAGETEMPLATE,

[crt].GETCUSTOMERIMAGEATTRIBUTEDEFAULTVALUE() AS CUSTOMERDEFAULTIMAGETEMPLATE

FROM [ax].RETAILCHANNELTABLE rct

-- Get transaction service profile information

INNER JOIN [ax].RETAILTRANSACTIONSERVICEPROFILE rtsp ON rct.TRANSACTIONSERVICEPROFILE = rtsp.PROFILEID

-- Get the store's country/region

INNER JOIN [ax].COMPANYINFO ci ON ci.DATAAREA = rct.INVENTLOCATIONDATAAREAID

INNER JOIN [ax].LEDGER l ON l.PRIMARYFORLEGALENTITY = ci.RECID

INNER JOIN [ax].DIRPARTYLOCATION drl ON drl.ISPRIMARY = 1 AND drl.PARTY = ci.RECID

INNER JOIN [ax].LOGISTICSPOSTALADDRESS lpa ON lpa.ISPRIVATE = 0 AND lpa.LOCATION = drl.LOCATION

INNER JOIN [ax].LOGISTICSADDRESSCOUNTRYREGION lacr ON lacr.COUNTRYREGIONID = lpa.COUNTRYREGIONID

-- Get the store's default language

INNER JOIN [crt].CHANNELLANGUAGESVIEW clv ON clv.CHANNEL = rct.RECID AND clv.ISDEFAULT = 1

INNER JOIN [ax].RETAILPARAMETERS rp ON rp.[KEY] = 0 AND rp.DATAAREAID = rct.INVENTLOCATIONDATAAREAID

CROSS JOIN [ax].RETAILSHAREDPARAMETERS rsp

LEFT JOIN [ax].RETAILSHAREDBINGPARAMETERS rsbp ON rsbp.[KEY] = 0

WHERE rsp.[KEY] = 0

)

Detailed Event Log:

LSRetailPosis.POSProcesses.ProcessInput: Microsoft.Dynamics.Commerce.Runtime.ConfigurationException: No channel configuration was found.

   at Microsoft.Dynamics.Commerce.Runtime.Data.ChannelDatabaseAccessor.GetChannelConfiguration(Int64 channelId)

   at Microsoft.Dynamics.Commerce.Runtime.Data.ChannelDataManager.GetChannelConfiguration(Int64 channelId)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.CreateRequestContext(Request request)

   at Microsoft.Dynamics.Retail.Pos.PriceService.Price.GetPrice(IRetailTransaction retailTransaction)

   at LSRetailPosis.BusinessLogic.ItemSystem.CalculatePriceDiscount(IPosTransaction transaction)

   at LSRetailPosis.BusinessLogic.ItemSystem.CalculatePriceTaxDiscount(IPosTransaction transaction)

   at LSRetailPosis.POSProcesses.ItemSale.Execute()

   at LSRetailPosis.POSProcesses.Operation.RunOperation()

   at LSRetailPosis.POSProcesses.ProcessInput.Execute().