Manipulation – MAN
Manipulation of the SYSPRO data is a constant requirement. Specific SYSPRO master files are periodically required to be re-structured due to aspects such as business growth and new strategies. These procedures will update the associated master files along with any occurrence of that master field throughout the transaction and history tables. This conversion process is based on a user defined cross reference which may be imported from “comma separated files” created using tools such as Microsoft® Excel®. Where necessary, these conversion procedures will merge data where many-to-one relationships exist. The following conversion procedures have been included in the MAN product at this stage:
· General Ledger code – GLAM
· Stock Code – SCAM
· Customer Code – ARAM
· Supplier Code – APAM
· Product Class – PCAM
· Product Class based on Stock Code – SPAM
· Warehouse Code – WHAM (NEW – Introduced for SYSPRO 7)
DOWNLOAD Introduction flyer
VIEW Installation and User Documentation
Validate old X-ref code: Validates the old cross reference code against the SYSPRO master table for validity
Audit report setting: Defines whether the audit information is written to the audit file in detailed or summary format
Code merging: Indicates whether or not the module may allow multiple old codes to be merged into a single new code
Monitor refresh delay period: Defines how often the status information will be refreshed if in “Monitor” mode
Module – select the associated module from the drop down
Save – selecting this save button will save the associated transactions and the validation routine will run showing the user a summary of the recorded transactions
Delete – will action the deletion of the highlighted entry in the Cross Reference Maintenance window
Preferences – will invoke the Manipulation Control / Preferences program
Re-validate – will force the revalidation routine which will refresh the various status flags accordingly.
Import File – selecting this button will present an Import File Details window. This import option caters for importing from a Comma Separated Value (CSV) file or alternatively the cross reference from another SYSPRO company
Print Xref Report – selecting this button will invoke the Cross Reference Details Report
Clear Entries – will display a confirmation prompt before removing all entries from the list view
MAN Xref Report
Module – select the associated module from the drop down
Show invalid entries – if this option is set to “Yes”, the report will include the pages showing the invalid cross reference entries along with all their associated errors
Show valid entries – if this option is set to “Yes”, the report will include the pages showing the valid cross reference entries
Module – select the associated module from the drop down
Source IT Disclaimer: Observe the disclaimer details
Accept Disclaimer – accept the disclaimer by changing this value to “Yes” before the “Begin Replacement” button may become active
Begin Replacement – selecting this button will begin the associated manipulation for the selected module
Module Information: Displays status of a specific module’s run along with the relevant settings
Table Details: Lists all the tables being modified by the current module along with status icon flags
Table Information: Displays the specifics of the records processed based on the highlighted table in the Table Details list.
MAN Audit Report
Clear Audit File – this button will delete ALL the Audit File records for the selected module.
Reprint records – if set to “Yes”, the report will be produced for all records in the selected date range whether previously printed or not.
Date selection – selecting “Single” will only allow entry of the “Date from” field. “Range” will allow entry of the “Date from” and “Date to” fields. If “All” is selected, all the unprinted audit records will print in date and time sequence.
Use with confidence
Please click on the SYSPRO Version you are running for More Information and/or To Download.
+ When upgrading MAN utility from version 6.1 to version 7.0 and the following message appears : “An unexpected message has been returned when accessing your data” – ‘File name: C:\SYSPRO61\DATA\2MANFCT.DAT’
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 and NEW Code, using a product such as Microsoft Excel, place the OLD Codes in the first column and the associated NEW Codes in the second column:
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:
+ Get message saying “Manipulation is currently being run” just after selecting to Begin Replacement
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:
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.
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.
+ During the conversion, I get a ‘Duplicate key’ error message, even though no duplicates exist on the table
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
+ Data or index file missing, Attempting to open a file. …\GENCTL.DAT 9-043 IMPSQM – SQL Copy Functions …
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”
“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…”
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
+ When trying to import the Xref file, I get the following error: Import file path – There are old codes that do not appear in SYSPRO
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.
+ When installing a Source IT product and you get a message, ‘SITCUS.IMP’ cannot be found please copy into CUSPROG folder
When running SITINI, do not tick the 4th block: “ Create or remove entries in CUSTOM.IMP”
+ Error copying message received: “There was a problem copying SITMEN.IMP from the programs directory to the customs program directory.Please check the relevant permissions and try again”
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:
- The SITMEN.IMP file is not in the program folder (put installation executable in SYSPRO60\base\upgrade folder and allow update)
- The folder specified in the system setup as per the screen image below has an issue (incorrect naming, permissions etc)
+ Running SYSPRO 6.1 SP1 and encounters “random characters” in the Source IT License “System:” field and a message “License Code is Invalid” are displayed when trying to license
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.
XML license uploads only function with CHROME browser