Wednesday, July 20, 2016

AX retail pull jobs not uploading

What to do when a pull job is not uploading transactions. For ex, sales transactions from POS to AX head office.

First thing to check is event viewer for any errors. If you don’t see any errors, you can try below method.

A bit of background first.

AX retail push jobs use SQL change tracking to push data from HO to store. But pull jobs use a different way, a replication counter.  Let’s see how it works for sales transactions.

In below example, RetailPOS is the store database.

Table [RetailPOS].[ax].[RETAILTRANSACTIONSALESTRANS] has a field called [REPLICATIONCOUNTERFROMORIGIN] that is an auto-incremented number, every new transactions gets a new number.
There is another table [RetailPOS].[crt].[TABLEREPLICATIONLOG] that stores a list of all tables that need to upload data. This table has a field FILTERMAX, which is a reference to the above field RETAILTRANSACTIONSALESTRANS.REPLICATIONCOUNTERFROMORIGIN
Field FILTERMAX is always up-to-date to indicate the last transaction uploaded.

That means any new transactions created in store DB will get a value in RETAILTRANSACTIONSALESTRANS.REPLICATIONCOUNTERFROMORIGIN greater than the one currently set in FILTERMAX. Every time the pull job runs, it checks REPLICATIONCOUNTERFROMORIGIN in all tables against the FILTERMAX value for those tables and sees if there are any new transactions that need to be uploaded, using the Async client service and sets the FILTERMAX values for the tables to the last uploaded value.

So one thing you could do is to force the store database into thinking that the records haven’t been uploaded to Async Server. If table [RetailPOS].[crt].[TABLEREPLICATIONLOG] was cleared out it would “reset” the store database and it would re-upload all records into a new RPF file.  This should be OK since any duplicate transactions would be ignored by AX.

Follow this steps:
1. Stop the AsyncClient in the StoreDB machine
2. Make a backup of the store Database
3. Delete the records in the table
4. Start the Async Client
5. Run the P-job


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [ID]
      ,[UPLOADSESSIONID]
      ,[TABLENAME]
      ,[FILTERFIELDNAME]
      ,[FILTERMIN]
      ,[FILTERMAX]
  FROM [RetailPOS].[crt].[TABLEREPLICATIONLOG]





/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [BARCODE]
      ,[ITEMCORRECTEDLINE]
      ,[ITEMID]
      ,[PERIODICDISCGROUP]
      ,[PERIODICDISCTYPE]
      ,[PERIODICPERCENTAGEDISCOUNT]
      ,[PRESCRIPTIONID]
      ,[PRICE]
      ,[PRICECHANGE]
      ,[PURCHID]
      ,[QTY]
      ,[REPLICATIONCOUNTERFROMORIGIN]
      ,[STORE]
      ,[TAXAMOUNT]
      ,[TAXGROUP]
      ,[TRANSACTIONCODE]
      ,[TRANSACTIONID]
      ,[CREATEDDATETIME]
      ,[MODIFIEDDATETIME]
      ,[CREATEDTRANSACTIONID]
      ,[MODIFIEDTRANSACTIONID]
      ,[DATAAREAID]
      ,[ROWVERSION]
  FROM [RetailPOS].[ax].[RETAILTRANSACTIONSALESTRANS]




1 comment: