Error Retail Statement Posting InventTransTMPOriginKeyValueTransfer

Error in Retail Statement Posting

Error Messages

Cannot create a record in Transfer of the InventTrans origin foreign key value (InventTransTMPOriginKeyValueTransfer). Inventory transactions originator: 1001/409666, 1001/409666. The record already exists.

Microsoft.Dynamics.Ax.Xpp.ErrorException: Exception of type Microsoft.Dynamics.Ax.Xpp.ErrorException' was thrown. at Dynamics.Ax.Application.RetailStatementPostSalesOrders.Run() in RetailStatementPostSalesOrders.run.xpp:line 129

at Dynamics.Ax.Application.BatchRun.runJobStaticCode(Int64 batchId) in BatchRun.runJobStaticCode.xpp:line 54

at Dynamics.Ax.Application.BatchRun.runJobStatic(Int64 batchId) in BatchRun.runJobStatic.xpp:line 13

at BatchRun::runJobStatic(Object[] )

at Microsoft.Dynamics.Ax.Xpp.ReflectionCallHelper.MakeStaticCall(Type type, String MethodName, Object[] parameters) at BatchIL.taskThreadEntry(Object threadArg)

Cause 

If you are getting error in Retail Statement posting and you are using Indian GST tax, then you are getting this error due to change HSN code into item master and push data into store database during the mid of the transaction, (Ex. Item A having HSN Code '123456' and you changed HSN Code '78910' on same day before shift close) then you will face this problem at the time of statement posting.

Solution
Identify your (Item,HSN Code and Transaction Time) from AX database RetailTransactionSaleTrans Table and break Statement into the part then post.

SQL Query

Create Function [Dbo].[Vijaysharma]  (@Timeinsec Varchar(Max))  
Returns Varchar(Max)  
Begin  
Declare @Op Varchar(Max)  
Set @Op = ''  
Select @Op=Right('0' + Cast(@Timeinsec / 3600 As Varchar),2) + ':' +  
Right('0' + Cast((@Timeinsec / 60) % 60 As Varchar),2) + ':' +  
Right('0' + Cast(@Timeinsec % 60 As Varchar),2)  
Return @Op  
End 
----------------------------Find Duplicate Records-------------------------------------------------------
Select Itemid, Concat(Hsncode_In,Itemid) As Hsnitem Into #Vijay From Retailtransactionsalestrans Where Transdate='2018-09-07' and store='004'
Select Distinct Hsnitem,Itemid Into ##Vijay From #Vijay 
Select Itemid, Count(1) As Duplicate Into ###Vijay From  ##Vijay Group By Itemid Having  Count(1) > 1;
Select Rt.Hsncode_In,Rt.Itemid,Rt.Transdate,Rt.Transtime,rt.store Into ####Vijay From  Retailtransactionsalestrans As Rt  Join  ###Vijay As Vi On Vi.Itemid=Rt.Itemid Where Rt.Transdate='2018-09-07' and store='004'
Select Hsncode_in,Itemid,transdate,store, [Dbo].[Vijaysharma](TRANSTIME) 'Time' into #####Vijay from ####Vijay order by itemid,Time
select min(time) 'Start Time',max(time) 'End Time',Itemid,Hsncode_in,Store from #####Vijay group by itemid,hsncode_in,store order by max(time),itemid
------------Delete Temp Tables------------------------------------------------------------------------------
Drop Table #Vijay ,##Vijay ,###Vijay,####Vijay,#####Vijay

Excel Sheet

Select Hsncode_in,Itemid from RetailtransactionSalesTrans where Transdate='2018-05-22'
Copy these records from the SQL Tables and paste into the excel sheet, Insert one more column and Put formula =concate(B1,C2)

Go to the Menu Data tab and Remove Duplicate from the concate column, after that select ITEMID column, go to the menu Home Tab click on Conditional Formatting--Highlight Cells Rules--Click on Duplicate Values, Select Duplicate with color. you will find duplicate values. You can check transaction time of that particular item and can calculate(Split) your statement accordingly.

Error in Sync Job 1130 and 1040 Product Not Sync

Error in Sync Product N-1040 and N-1130 Product Availability
("Process Target Request Header Failed Due to An Invalid Operation Exception")

When running the sync job from Distribution Schedule, we came across this error - "System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.".

Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.
connectionString: Data Source=CDB;Initial Catalog=RetailBostanciTestStore;Integrated Security=True;Connect Timeout=60;Application Name="Commerce Data Exchange Async Client" ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.

1. Normally this error comes due to mismatch both databases (AX and Channel DB), We need to Identify which table and which column has changed.
2. Database Version Mismatch like any CU update or Retail Channel Utility  or Channel database not updated.

I found two tables which is having issue

* ECORESPRODUCTTRANSLATION ( Column Name)

* INVENTDIM ( Column WMSLOCATIONID)

Run Query Into SQL on Channel Database 

Alter Table ECORESPRODUCTTRANSLATION  Alter Column Name Nvarchar (90) Not Null

Alter Table INVENTDIM Alter Column WMSLOCATIONID Nvarchar (15) Not Null

Retail Receipt Backup

If you are going to modify your receipt format and want to take backup of your current receipt format.

Setps :

1. Open SQL go to the Microsoft Dynamics ax database
2. Open  table [RETAILFORMLAYOUT]
3. Run Query
   
SELECT  [HEADERXML]  ,[LINESXML],[FOOTERXML],*  FROM [MicrosoftDynamicsAx].[RETAILFORMLAYOUT]

4. Copy Headerxml,Linesxml,footerxml against the receipt ID

You can keep in xls or notepad these lines, whenever you want then you can replace with current lines.

Transaction missed from store to ax (Upload P- Job)

Statement and Shift Report not matching with each other, this is due to Sync transaction missed retail channel database to AX Database

How to match the record Channel Database to AX Database Run below Query  Both databasese and count the record :-

select STAFFID,TRANSACTIONID,RECEIPTID,BARCODE,ITEMID from RETAILTRANSACTIONSALESTRANS where TRANSDATE='2018-07-12' and STAFFID='000566' and  RECEIPTID!=''

select STAFF,TRANSACTIONID,RECEIPTID from RETAILTRANSACTIONTABLE where TRANSDATE='2018-07-12' and STAFF='000566' and RECEIPTID!='' order by RECEIPTID

This Problem comes due to mismatch Region/Date/Time Zone 

1.   At the Store Level Stop the Async Client Service (If You Have This Service Set to Automatically Restart on Failure Make Sure You Turn This Off In The Properties For The Service)

2.   Ensure That the POS Application Is Closed (Or At Least Not Being Using at The Time)

3.   Open SQL Server Management Studio and Connect to The SQL Server Hosting Your Channel (Store) Database

4.   Run the Following Query Against Your Channel Database DELETE FROM CRT.TABLEREPLICATIONLOG

5.   Once This Completes, Start the Async Client Service on The Machine (This Will Trigger Async Client to Check for Any Records to Upload)

6.   Either Run the Below Query Against Your Async Server Message Database, Or Check Your Upload Work Folder to Confirm A New Package File Has Been Created SELECT * FROM UPLOADSESSION ORDER BY ID DESC

7.   Once You See the New Package File, Go Into AX, And Select to Run The P-Job.  

8.   If There Are No Errors Running The P-Job, Go to Retail > Inquiries > Commerce Data Exchange > Upload Sessions 

9.   You Should See an Entry in This Form with The Column Date Uploaded Showing the Date and Time You Ran The P-Job

10.   If You See the Entry in This Form, Then Everything Worked, And You Can Create/Calculate/Post Statements for The Store.


Schedule Store Procedure to Delete Sync Transaction from Store Database :-

USE [ProdDynamicsAX]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[Admin_POS_AutoSync] as

declare @dbcount as int

declare @aoscount as int

--Store 1 SUPERMARKET

--------------------------------------------------------------------------------------

set @aoscount=0

set @dbcount=0

select @aoscount=count(*) from  dbo.RETAILTRANSACTIONSALESTRANS where convert(date,TRANSDATE,103)=

convert(date,getdate()-1,103)

and  store='001' and RECEIPTID!=''

select @dbcount=count(*) from  [192.168.2.55].ProdSPM001.ax.RETAILTRANSACTIONSALESTRANS where

convert(date,createddatetime,103)=convert(date,getdate()-1,103)

if @aoscount<>@dbcount

       delete from  [192.168.2.55].ProdSPM001.crt.tablereplicationlog

--select @aoscount

--select @dbcount

set @aoscount=0

set @dbcount=0

select @aoscount=count(*) from  dbo.RETAILTRANSACTIONTABLE where convert(date,TRANSDATE,103)=

convert(date,getdate()-1,103)

and  store='001' and RECEIPTID!=''

select @dbcount=count(*) from  [192.168.2.55].ProdSPM001.ax.RETAILTRANSACTIONTABLE where

convert(date,createddatetime,103)=convert(date,getdate()-1,103)

if @aoscount<>@dbcount

       delete from  [192.168.2.55].ProdSPM001.crt.tablereplicationlog

--select @aoscount

--select @dbcount

-------------------------------------------------------------------------------------

--Store 2

-------------------------------------------------------------------------------------

set @aoscount=0

set @dbcount=0

select @aoscount=count(*) from  dbo.RETAILTRANSACTIONSALESTRANS where convert(date,TRANSDATE,103)=

convert(date,getdate()-1,103)

and  store='002' and RECEIPTID!=''

select @dbcount=count(*) from  [192.168.2.55].ProdFST002.ax.RETAILTRANSACTIONSALESTRANS where

convert(date,createddatetime,103)=convert(date,getdate()-1,103)

if @aoscount<>@dbcount

       delete from  [192.168.2.55].ProdFST002.crt.tablereplicationlog

--select @aoscount

--select @dbcount

set @aoscount=0

set @dbcount=0

select @aoscount=count(*) from  dbo.RETAILTRANSACTIONTABLE where convert(date,TRANSDATE,103)=

convert(date,getdate()-1,103)

and  store='002' and RECEIPTID!=''

select @dbcount=count(*) from  [192.168.2.55].ProdFST002.ax.RETAILTRANSACTIONTABLE where

convert(date,createddatetime,103)=convert(date,getdate()-1,103)

if @aoscount<>@dbcount

       delete from  [192.168.2.55].ProdFST002.crt.tablereplicationlog

--select @aoscount

--select @dbcount