Skip to main content
Dynamics 365
·
2 min read

AX for Retail: Manage data cleanup task in POS database

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

ktruelse

Related posts