Manipulation – MAN

To resolve, the simple solution will be to delete the 2MANFCT.DAT and 2MANFCT.idx files found in the SYSPRO Data folder

The StockCode ProductClass Account Manipulation (SPAM) module steps through all the SYSPRO tables and any transaction that contains both the Stock Code and Product Class will be modified based on the Xref which will contain the OLD Product Class, Stock Code and NEW Product Class. On each relevant SYSPRO transaction, ONLY the OLD Product Class will be changed to the NEW Product Class.

This solution came about with our clients only needing to change the Product Class assigned to a specific Stock Code and also modify the historical transactions to reflect the NEW Product Class. Since there is no one-to-one relationship between OLD and NEW Product Class, the SPAM product does not make any changes to any table which contains ONLY the Product Class and the Product Class master is therefore not modified.

Some of our clients have then used our Product Class Account Manipulation (PCAM) to make the necessary modifications to the master tables etc after using the SPAM module to “clean up” but this is not an absolute requirement for our SPAM clients.

The Xref import file would therefore require three columns with the OLD Product Class in the first column, the Stock Code in the second column and the NEW Product Class in the third column:

For all the MAN modules simply requiring the OLD Code, NEW Code and optionally the NEW description, using a product such as Microsoft Excel (as per the below image), place:
– the OLD Codes in the first column
– the associated NEW Codes in the second column
– the optional NEW descriptions in the third column
and then save the spreadsheet as a CSV (Comma delimited) (*.csv) format which will be able to be imported into the required cross reference module.

The StockCode ProductClass Account Manipulation (SPAM) module requires OLD Product Class, Stock Code  and NEW Product Class and would therefore require three columns with the OLD Product Class in the first column, the Stock Code in the second column and the NEW Product Class in the third column:

Saving this as a Comma Separated Value (.CSV) to be used by the MAN Xref import process would look like this:

AFTER PREMATURE TERMINATION: The MAN product has been developed to explicitly prevent the same module from being run more than once at a time. By nature of this process and the possibilities of unforeseen failures, both hardware and software related, the module being run before the failure is unable to be rerun after the problems have been resolved. As an interim solution, we have a “RESET” utility program that may be used to reset that module. Using File\Run a program (Control+R) and enter MANRES before selecting OK to run. Choose the relevant module as per figure 1 below and select the Reset button.

As each of the Source IT packages shares the same configuration procedure (SITINI), each package installation and configuration needs to be handled separately.
For each package being installed, place the downloaded package executable into the SYSPRO\base\upgrade folder, login to SYSPRO as a SYSPRO Administrator and allow for server update  to take place, log back in and run the configuration program (SITINI).

NOTE: The configuration program (SITINI) will only process the last package installation file updated onto the server.

Upgrading multiple Source IT packages to a newer SYSPRO version when they originally existed in the previous SYSPRO version does not require the configuration program (SITINI) to be run and the server update may be actioned using multiple package executables simultaneously by loading all of them into the SYSPRO\base\upgrade folder on the server and logging in as a SYSPRO Administrator .

Our packaged products include a 30 day test license allowing for them to be installed in SYSPRO and used to determine whether the included functionality is adequate.
Note that our MAN product test licenses are limited to a single code or single merge.

If extension license codes are required to complete testing, feel free to contact us to arrange associated licenses.

SYSPRO have a utility to test the BULK INSERT routine as used by the MAN modules.

This may be used to resolve the following common file status’s:
37 – Invalid permissions
9-??? – Invalid folder (??? denotes any 3 character numeric value e.g. 9-009)

On the SYSPRO Main Menu:
Utilities \ SQL Server Utilities \ Diagnostic Query (or Run Program “IMPDG6”)

Select the “Function” option in the program menu and “Test Bulk Insert command…”

Select “OK” to allow test to run. If all is well, you should receive a success message.

If the folder specified in the “Temporary folder:” is invalid, has inadequate permissions or insufficient space available, an associated message will be displayed.

Invalid folder specified:

Inadequate permissions:

Failure message:

Whether licensing any of the Source IT packaged products or bespoke development, the license entry is to be done for each SYSPRO company as required.

Select the Source IT license option based on the process being relicensed (see the highlighted options indicated below)

NOTE: If the Source IT product menu option does not exist on your Program List, see this FAQ.

The license information is entered into the Source IT License Control screen as per image below.

NOTE: As of August 2016, the is a new “V1” license version as per the representation in the title bar. This “V1” representation will also be visible on the Source IT license report.

Expiry date – enter the license expiry date as per the Source IT license report. To ensure correct date format, make use of the date browser to select the expiry date.
New Code – enter the license code as issued by the Source IT offices.
Ignore warnings – make sure that this checkbox is unticked.  This will ensure that the expiry warning message will be displayed in the month of expiry.
Evaluation License – only tick this checkbox when the Source IT license report indicates that the license is an evaluation license.
Save – will validate the entered values and verify if the license code is valid for the expiry date based on specifics pertaining to the SYSPRO registered company currently logged into. If deemed valid, the information will be stored as entered.
Close – will exit the program without validating the information on the screen. If the “Ignore warnings” checkbox status was modified, the new setting will be applied to the license validation process in the future.

The license process is written to provide a warning message each time it is run in the expiry month indicating the number of days until the license expires as per below figure. This functionality is deactivated by ticking the “Ignore warnings” option on the above License Control screen.

The time required for each MAN module to complete is primarily based on the amount of data stored in the associated SYSPRO tables.
Other aspects such as the server processing capacity and network speeds will naturally influence run times.
Run times vary from literally seconds through to a few hours with our longest reported time at 7 hours for GLAM on a 130GB database back in 2009. Average reported run times have been between 1 and 2 hours.

The MAN modules processes each relevant table and compares the code on each transaction against the cross reference to verify if it is required to me modified.
Based on this solution, the impact of the number of cross reference entries has a minimal effect on the total run time.
If a single cross reference entry takes 2 hours to complete, using 2000 cross reference entries would add about 15 minutes onto the run time.
Aspects such as “summary audit” settings against the MAN module will also have a bearing. There is no practical reason why the “detailed audit” option would be specified but this setting would also have an implication on the run rime adding up to another 20 minutes to the total run time.

The MAN module “Monitor refresh delay period” value you have set to 1 hour does have an implication but nothing like the Tick Over. This value would have been fine at 1 minute to not have a huge implication on your volumes.

IMPORTANT:
The “Tickover refreshes on reports/batches” value as defined against the SYSPRO user being used to run the MAN module must be set to at least “2 minutes” as this is CRITICAL for the optimal performance of our manipulation product.

If you are able to see values ticking over in the “Stock Code Replacement In Progress” window, the “Tickover refreshes on reports/batches” value is incorrect. This session should even indicate “Not responding” due to the 2 minute PLUS delay between refreshes. It is the primary reason we have the “Manipulation Module Status” tool to allow real time viewing of the modules progress.

To modify this “Tickover refreshes on reports/batches” value against the operator, use the SYSPRO “Operator Maintenance” program as follows:

All our data manipulation modules have been developed to handle merging of data within a single SYSPRO company where possible.

Due to the nature of differing rules associated to merging from business to business, it may be necessary to clarify these rules and even run conversions using live test data to confirm acceptance of merging rules.

If the table being altered has the Old Code as a key and the NEW code already exists in SYSPRO, the program will merge the records with the data of the existing master transaction along with quantities, balances, budgets etc. may need to be added together.

Code merging – When replacing a FROM code with a TO code that already exists in SYSPRO, the static information of the FROM record will be lost and this same information of the TO code will remain in SYSPRO. Examples of these are: Dates, Addresses, Names, Rates, Prices, Credit Limits etc. Other than this static information, where at all possible, the information from both records will be merged. It is therefore assumed that when a merge manipulation occurs, the two records being merged have the same unit of measure, rate, price, currency, etc.

This is caused by certain record types being non-duplicate in SQL, but having duplicates when being copied across to ISAM e.g. INVMOV – EntryDate contains both the date and time, but TrnTime contains a zero.  When this table is copied across to ISAM for conversion, if the key is duplicated (same stock code, warehouse, transaction year, transaction month and entry date), ISAM will only copy the date part of EntryDate across, and not the time (this is how the file is defined), which will result in a duplicate.  Data created in SYSPRO would have the time stored in the TrnTime field, and not as part of the EntryDate field, which usually means this data has been generated from elsewhere.  The integrity of the data is a problem and needs to be fixed!

Example of error message:
SYSPRO File Message
Message:  Duplicate key
Access:  Attempting to write a record
File name:  this will vary
Status:  22

When starting the manipulation process, the following message is shown as per figure 11.1:
“Data or index file missing”
“Attempting to open a file”
“…\GENCTL.DAT”
“9-043”
“IMPSQM – SQL Copy Functions …”

This is then followed by the following message:
“Due to failure with SYSPRO Bulk Copy SQL to ISAM, Program will terminate…”

We wrote a “test” routine to check these settings which is why you received the message at the end about the failure and that no data was modified.

What we have done is to test a number of things up front when doing a conversion:
• check sql login information i.e. check system admin login (SQL server setup)
• check the temporary folder has been set up for the bulk copy (SQL server setup)
• a test is done to ensure that we can connect to the datasource
• a test is done to build the path and open GENCTL (pathing will be done in ISAM or SQL depending on your company setup), and then to read the table to make sure there is a record in it
• a test is done to make sure that GENCTL can be copied from SQL to ISAM (using the bulk copy process in IMPSQM)
• a test is done to make sure that GENCTL can then be copied from ISAM back to SQL (using the bulk copy process in IMPSQM)
In SYSPRO select FILE -> RUN -> SDKSQM

After running ‘SDKSQM’,copy the GENCTL to see if original error appears:
“Due to failure with SYSPRO Bulk Copy SQL to ISAM, Program will terminate…”

Solution consideration:

The temporary folder has not been configured in the company setup – SQL Server Tab . This is for the BULK INSERT and is only used when copying from ISAM to SQL. This directory is NOT on the client computer as it must be available from SQL Server. It must exist, have adequate permissions and have sufficient space available for copying of files from SQL to ISAM, as this folder is used by SYSPRO’S IMPSQM process as a temporary storage area. It must also be accessible to both the APPLICATION SERVER and SEQUEL SEVER

In the control/preferences program, if the ‘Validate old X-ref code’ is set to “Yes”, and the file imported contains “OLD” codes which do not exist in SYSPRO, the error message above will be generated.
Change this “Validate old X-ref code” setting to “No” to ignore the validation.

This typically occurs on a multi-tier environment where SYSPRO and SQL are running on separate server.

To correct, change the “Bulk insert temp folder” to address a temporary folder on the SQL Server itself and ensure that this folder has adequate permissions and sufficient free space available.

When running SITINI, do not tick the 4^th block: “ Create or remove entries in CUSTOM.IMP”

When you run the Source IT initialisation setup program (SITINI) process to setup menus etc. the routine attempts to copy the SITMEN.IMP file (and other .IMP files) into the folder you specified in the Custom programs folder

This usually relates to 1 of 2 problems:

1. The SITMEN.IMP file is not in the program folder (put installation executable in SYSPRO60\base\upgrade folder and allow update)
2. The folder specified in the system setup as per the screen image below has an issue (incorrect naming, permissions etc)

This particular license issue (random characters in “System:” code field) relates to the fact that for SYSPRO 6.1 SP1, we needed to make licensing modifications to our products to accommodate the SYSPRO APP Store licensing. Please FIRST DOWNLOAD the latest version of BTS (make reference to BTS installation manual) and ensure that client is on minimum PORT 007 of SYSPRO 6.1 SP1, ALSO ensure the BTS triggers are pointing to the current version of the trigger programs and that the SYSPRO 6.1 SP1 installation has been installed in a new folder location (BTS triggers MUST NOT point to the old version of the trigger programs on SYSPRO 6.1 that client was running). To automatically correct trigger entries, manually remove the associated triggers and run SITINI program to setup triggers correctly.

Usually encountered when the customer is using one of the MAN products which has the new license solution.