Compare & Set Delete |
|
The Compare & Set Delete is an SQL interface to a host system that compares the existing orders in a host database to the existing orders in the DMS database. If the order does not exist in the host system, but does exist in the DMS database, the order in the DMS database will have its Job Status set to a user given value.
The status of a Compare & Set Delete execution is logged in the database. To view the log, select View Log from Compare & Set Delete or from the DMS Monthly Calendar Menu, select Interface | Display Logs.
The compare and set delete has an SQL script that need to be defined as well as the execution of the command either manually or by a command file:
1.Compare & Set Delete SQL Scripts
3.Auto Execute Compare & Set Delete
Compare & Set Delete SQL Scripts
Before adding a new script to compare and set delete, a connection string to the host must be setup in DMS (see External Database Connection for more information). Also, a Job State to set the Jobs not found in the host system needs to be added to DMS. To easily identify jobs not in the host system, the Job State could be something like Deleted in CP or Deleted in Host, etc.
To define Compare and Set Delete SQL scripts, select Interface | Import settings | Compare & set Delete SQL Scripts. A window will appear with the current list of defined SQL scripts in the tree. The example below shows two defined SQL scripts in the tree:
The following prompts are required for setting up the Compare & Set Delete:
1.Script Name The name of the Compare & Set Delete script. The name must be unique.
2.Connection String The name of the connection string.
3.Set to Job State The job state to set a job to if it is not found in the host (i.e. Deleted in CP or Deleted in Host, etc.).
4.Host Table Name The name of the database table in the host that has the jobs that the Compare & Set Delete will use for comparison.
5.Host Order Number Column The host’s table Order Number column name (i.e. MO).
6.SQL Where Clause The SQL Where clause for the host to be used to locate the jobs for the Compare & Set Delete.
7.Include DMS Order Numbers The DMS order numbers to include in the compare. More than one order number can be entered where each order number is separated by a semi colon. DMS uses the order numbers entered in an SQL “like” clause to find all order numbers in DMS that start with the order numbers entered. For example, if “MO; SO” was entered, DMS will search for all order numbers that start with MO or SO.
8.Exclude DMS Order Numbers The DMS order numbers to exclude in the compare. More than one order number can be entered where each order number is separated by a semi colon. DMS uses the order numbers entered in an SQL “like” clause to find all order numbers in DMS that start with the order numbers entered. For example, if “SHIP; OUT” was entered, DMS will exclude for all order numbers that start with SHIP or OUT.
9.Select Job States to compare for delete One or more Job States can be selected for the compare. Click on the dropdown icon and select the Job States to include in the search. To close the menu, click on the dropdown icon again.
10.Press Save to save the script or press Cancel to cancel the save/edit.
The Compare & Set Delete runs the defined SQL script and is executed from either a command file or from the DMS Monthly Calendar Menu, Interface | Compare & Set Delete as per the example below:
To view the logs of Compare & Set Delete, click on the Display Log button.
Enter the Script Name to run and press OK. DMS confirms the run with the window:
Press OK to run the script or press Cancel to cancel the run.