Purging "non-PAL" Records from SMS Database

In a multi-store environment, it sometimes happens that your SMS host database can become cluttered with various types of product records with a target (F1000) other than 'PAL' and which you would prefer go away.

How/why exactly this comes about is not fully understood by the author and therefore will not be explained here. Also how to prevent the problem will not be explained, for the same reason. It is assumed that if the reader has this issue then perhaps they do not (need to) understand these finer points either, but rather just want to fix the problem...

Overview

The 'rattail-locsms' package comes with a command which can generate SIL to purge all non-PAL records from the host database. It supports three tables: POS_TAB, COST_TAB and PRICE_TAB. The command will connect to the host database and query it to locate the offending records, then produce a SIL file(s) (one per table) instructing SMS to delete the records.

The command does not attempt to deploy the SIL to any SMS inbox; however you may specify the output folder for the SIL file(s). Should you choose to deploy the SIL to SMS, you must do so manually.

Installation

Install Rattail as usual, but also do:

pip install rattail-locsms

Configuration

In addition to typical Configuration considerations, this tool requires you to configure the connection to the SMS database. Here is a sample config snippet:

[rattail_locsms]
sqlalchemy.url = mssql://rattail:password@custom-sms-dsn

Where 'rattail' and 'password' are valid credentials to the SQL Server instance and database represented by 'custom-sms-dsn', which is an ODBC DSN. Read more about that for Windows and Linux.

Note that on Windows it should be possible to store the credentials within the DSN itself in which case they should not be needed within the config file. Also of course there is the possibility of using Windows authentication instead of SQL authentication (which is implied by the use of credentials). However the author has yet to attempt Windows authentication, as all work is done from Linux. If you try and have clues / questions, let me know.

Usage

Usage is pretty straightforward:

rattail sms-purge-non-pal --help

A more complete form of the typical usage might be:

rattail --config=custom.conf sms-purge-non-pal --progress --output-dir=some-folder POS Cost Price

Note that those 3 nouns at the end are optional. You may specify one or more of them to limit the scope of the operation; if you don't specify then all tables will be inspected.

Note also that if you specify an output folder, it must exist already; the command will not create it. It defaults to the current working directory.