Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support teams Worldwide

AX for Retail: Manage data cleanup task in POS database

AX for Retail: Manage data cleanup task in POS database

Rate This
  • Comments 1

Description:  

The cleanup procedures in POS are not fully implemented and therefore a small manual job can ensure that your database is not filling up with unnecessary records.

Resolution:

We create a job in SQL to run in a schedule. In this example we use 150 days. All Transactions that exceeds that date will be deleted.

The number of days depends on the frequency of the replication (P-JOB) and normally the value would be much lower like 1-5 days.

 

1. Go to your SQL Management studio

2. Go to Maintenance Plans

3. Create new plan and call it example DeletePOSData

 

4. Move over the Execute T-SQL Statement Task

5. Edit the TASK and add the delete statements

USE AXRETAILPOS
GO
-- All POS transactions tables
-- All records that are more than 150 days will be deleted

DELETE FROM dbo.RBOTRANSACTIONBANKEDTENDE20338
WHERE transdate < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONINCOMEEXPEN20158
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONINFOCODETRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONINFOCODETRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONINVENTTRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONLOYALTYPOIN20296
WHERE createdDate < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONLOYALTYPOINTTRANS
WHERE createdDate < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONLOYALTYTRANS
WHERE REPLICATED = 1;

DELETE FROM dbo.RBOTRANSACTIONMIXANDMATCHTRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONORDERINVOICETRANS
WHERE REPLICATED = 1;

DELETE FROM dbo.RBOTRANSACTIONPAYMENTTRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONSAFETENDERTRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONSALESTRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONSALESTRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONTABLE
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONTENDERDECLA20165
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

DELETE FROM dbo.RBOTRANSACTIONVARIANTTRANS
WHERE TRANSDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

-- POS log table
-- ALL data that is more than 150 days will be deleted

DELETE FROM dbo.POSISLOG
WHERE LOGDATE < DATEADD(day, -150, CAST(GETDATE() AS date));

 

6. Edit the Job schedule and let it run every day

7. Save the Maintenance job

8. Make sure that your SQL Agent runs, so the job executes every day

 

 

Author: Kim Truelsen

Blog date: 12-2-2012

Leave a Comment
  • Please add 2 and 2 and type the answer here:
  • Post
  • Now this time I visit many website and found many information but I think this is very important information all of us.I will sharing this information some other people.Thanks for published this information…

    <a href="flavors.me/dirkkettlewell">Dirk Kettlewell</a>

Page 1 of 1 (1 items)