|
Quantum VS Help: File Maintenance |
IMPORTANT: It is intended that all procedures for updating Quantum VS data with the latest Luckins data will be automated using a server script run as a cron job, with Quantum VS Highway used to transfer files. Therefore Quantum VS users/merchants should not have to follow these procedures. However, the set-up and manual procedures for loading the Luckins data and subsequently updating Quantum VS with the latest Luckins data are provided below.
Luckins catalogue data is made available via their FTP site. See:
Before loading any catalogue data you will need to create the required file structure on the Quantum VS server - see Creating The 'Luckins' File Structure On The Quantum VS Server.
The Luckins Distributors Database is supplied as an initial file to load the catalogue data. Follow the 'create' procedure to populate the 'Luckins' data tables in the Quantum VS database, enabling Luckins catalogue data to display in Quantum VS. See Loading The Initial Luckins Catalogue Data.
Subsequently, incremental Luckins 'revision' files are made available on a weekly basis. Follow the 'update' procedure to update the data tables in the Quantum VS database, ensuring up-to-date Luckins product, price and supplier data displays in Quantum VS. See Applying Incremental Luckins Catalogue Updates.
An additional process needs to be run to update any Quantum VS Products which are linked to - i.e. have been converted from - a Luckins Product and have any of the Luckins Update fields checked in their Product record. This will update the product's Description, Supplier and/or Price details (depending on which of the Luckins Update fields have been checked in their Product record in Quantum VS). See Updating Luckins Data For Converted Products.
Note: For further details see Luckins Product Maintenance and Luckins Database And Quantum VS Integration: Overview. If you have any problems please contact Quantum VS Professional Services on 01342 333807 or psg@edp.co.uk.
The Luckins Distributors database is made available from the Luckins FTP site: ftpstamford.amtech.co.uk. Using FTP you will need to transfer the files from the Luckins FTP site to the Quantum VS server.
Note: (1) You will need to obtain a login and password - contact http://www.luckinslive.com/. (2) We recommend using the FTP client Filezilla for downloading files. Other FTP clients are available. (3) Luckins catalogue data is also provided on CD, via email and by other means by request.
When you connect to the Luckins FTP site, the following directories are available:
create: You will use this to load the initial Luckins data set, using the most recent version. This is only applied once (assuming there are no problems).
revision: You will use this to apply catalogue updates once the initial data set has been loaded.
Note: A third option - special - may be obtained by request if there is a problem with the Luckins data set (e.g. when there is a data integrity issue). Otherwise this will not be required.
The Luckins 'create' and 'revision' directories will contain 'issue' directories called, for example, iss0805, iss1053, iss1054, iss1055 - referring to Issue Number 0805, 1053, 1054 and so on.
Within each 'issue' directory are a set of files with the following extensions:
CTL – This is the Control file for this Distributors Database Service release.
CSV – These are the database Data files for this Distributors Database Service release, which will populate/update the item details, supplier, and so on. These are in CSV Format (Comma Separated Variable), with the first record of each file containing the field names.
BLN – This is the Bulletin text file for this Distributors Database Service release. This is a flat ASCII text file.
The file naming convention is: File ID (one character), Service Code (three numerals), Issue Number (four numerals), Extension (three characters).
For example: For service 70, Issue Number 1234, you will see a directory called iss1234 containing the following files:
|
File Name |
Luckins File Description
|
Maps To Quantum VS Table |
|
A0701234.CTL
|
Control File |
luckins_control_file_header and luckins_control_file |
|
B0701234.CSV
|
Item Details
|
luckins_item_details |
|
C0701234.CSV
|
Supplier (Manufacturer) Index
|
luckins_supplier_details |
|
D0701234.CSV
|
Product Range Index
|
luckins_product_range |
|
E0701234.CSV
|
Commodity Code Index
|
luckins_commodity_code |
|
F0701234.CSV
|
Price Qualifier Index
|
luckins_price_qualifier |
|
G0701234.CSV
|
Price Type Index
|
luckins_price_type |
|
H0701234.CSV
|
VAT Rate Index
|
luckins_vat |
|
I0701234.BLN
|
Bulletin Details
|
(Not installed as a table; this is a text file only.) |
Note: Numerals are padded with zeros as required.
The 'catalogues' schema in the Quantum VS database will hold the following tables for Luckins-related data:
data_index
luckins_commodity_code
luckins_control_file
luckins_control_file_header
luckins_item_details
luckins_price_qualifier
luckins_price_type
luckins_product_range
luckins_supplier_details
luckins_vat
These tables are created/populated as part of the 'create' procedure and subsequently updated as part of the 'update' procedure; the Luckins data files (e.g. the Item Details .CSV file) map automatically to the equivalent Quantum VS table (e.g. luckins_item_details). Note that:
The 'data_index' table is an automatically maintained table of all of the Luckins product and supplier words that may then subsequently be used by Quantum VS search engines.
The 'luckins_control_file_header' will show the history of Luckins 'Service Code', 'Issue Number', 'Motive', and 'Issue Date' data fields of the initial 'create' and all subsequent 'revision' and 'special' updates thereafter.
The 'luckins_item_details' will hold all of the Luckins products. The 'short_description' and 'weee_status' columns are Luckins optional extras and therefore may not be populated. The additional columns of 'last_service_code', 'last_issue_number,', 'last_motive', and 'last_issue_date' have been added by EDP, allowing easy identification of the last Luckins Distributors Database update for each individual Luckins product row.
The 'luckins_supplier_details' will hold all of the Luckins suppliers and manufacturers. The 'web_address' and 'sale_email_address' columns are Luckins optional extras and therefore may not be populated.
The 'luckins_vat' Table currently holds four different VAT codes:
0 = 'Zero Rated (Non UK)'
1 = 'Zero Rated (UK)'
3 = 'Standard rate (UK)'
5 = 'Reduced Rate (UK)'
When adding a Luckins product, the import process will perform a 'best effort' attempt to correctly map the Luckins VAT Code with its equivalent application specific QuantumVS VAT Code Table, albeit that a QuantumVS VAT Code of 1 is explicitly reserved for the Standard Rate of VAT, thereby making Standard Rate mappings the simplest of them all.
Before loading any catalogue data you will need to create the following file structure within the usr/bct directory on the Quantum VS server:
/usr/bct
/luckins
/new
/update
Note: 'new' will be used for Luckins 'create' files; 'update' will be used for Luckins 'revision' files.
To do this, log on to the Quantum VS server as root and enter:
cd /usr/bct
mkdir luckins
cd luckins
mkdir new
mkdir update
Note: Optional: use the pwd command to check you are in the correct directory; use the ls or ls -l commands to list files in the current directory.
To load the initial Luckins catalogue data:
1. Using your FTP client:
Connect to the Luckins FTP site - see Obtaining Luckins Catalogue Files Using FTP; then
Locate the most recent 'create' directory (e.g. iss0805), then copy across all of the files within it to your local PC; then
Connect to the Quantum VS server and transfer these files to /usr/bct/luckins/new
Therefore within /usr/bct/luckins/new you should have one .CTL file, a series of .CSV files and a .BLN file, which you copied from the Luckins 'create' directory with the most recent issue number. See About The Luckins Data Files above.
2. Make sure you are logged on to the Quantum VS server as postgres.
3. Navigate to the directory 'new', by entering:
cd /usr/bct/luckins/new
4. Run the luckins_update.pl perl script appended with the file path which includes the name of the .CTL file you copied across.
For example, if the current working directory is /usr/bct/luckins/new which contains A6070805.CTL, enter:
luckins_update.pl /usr/bct/luckins/new/A6070805.CTL
Running this script populates the Luckins-related data tables on the Quantum VS server. These are held within the 'catalogues' schema within 'quantumvs'.
Note: The script takes a few minutes to finish running. A 'Completed' message will display when complete.
5. You may now log in to the Quantum VS application and check the Luckins Product data displays within the Product Finder. See Using The Product Finder To View Luckins Products.
Notes: (1) It may be necessary to restart services on the Quantum VS server before doing this. (2) Ensure you have enabled all Luckins-related features within Quantum VS and set up the Luckins Default Product Record. See Setting Up Luckins Product Maintenance.
Once the initial Luckins data set has loaded you may wish to:
Convert Luckins products into standard Quantum VS Products. See Converting Luckins Products Into Quantum VS Products.
Apply the incremental Luckins 'revision' updates. See Applying Incremental Luckins Catalogue Updates.
Incremental Luckins 'revision' files are made available on a weekly, or sometimes daily, basis. Luckins updates are available every week on Wednesday night for electrical products and Thursday night for other industries. The file size is around 5-10MB.
Follow the procedure below to apply a Luckins catalogue data update within Quantum VS:
Note: The following manual procedure may be automated using a server script run as a cron job, with Quantum VS Highway used to transfer files.
1. Using your FTP client:
Connect to the Luckins FTP site - see Obtaining Luckins Catalogue Files Using FTP; then
Locate the most recent 'revision' directory (e.g. iss1055), then copy across all of the files within it to your local PC; then
Connect to the Quantum VS server and transfer these files to /usr/bct/luckins/update
Therefore within /usr/bct/luckins/update you should have one .CTL file, a series of .CSV files and a .BLN file, which you copied from the Luckins 'revision' directory with the most recent issue number. See About The Luckins Data Files above.
2. Make sure you are logged on to the Quantum VS server as postgres.
3. Navigate to the directory 'update', by entering:
cd /usr/bct/luckins/update
4. Run the luckins_update.pl perl script appended with the file path which includes the name of the .CTL file you copied across.
For example, if the current working directory is /usr/bct/luckins/update which contains A6070806.CTL, enter:
luckins_update.pl /usr/bct/luckins/update/A6070806.CTL
Running this script updates the Luckins-related data tables on the Quantum VS server.
Note: The script takes a few minutes to finish running. A 'Completed' message will display when complete.
5. You may now log in to the Quantum VS application and check the Luckins Product data has updated within the Product Finder. See Using The Product Finder To View Luckins Products.
Notes: It may be necessary to restart services on the Quantum VS server before doing this.
This procedure described in Applying Incremental Luckins Catalogue Updates will update data for all non-converted Luckins Products in Quantum VS.
However, an additional process needs to be run to update any Quantum VS Products which are linked to - i.e. have been converted from - a Luckins Product and have any of the Luckins Update fields checked in their Product record. This will update the product's Description, Supplier and/or Price details in Quantum VS, depending on which 'Luckins Update' fields are checked in their Quantum VS Product record.
To update Luckins data for converted products:
Note: The following manual procedure may be automated using a server script run as a cron job.
1. Log onto the Quantum VS server as op1unix.
You may potentially log in as a different user, provided:
the COBOL environment has been set up on the Quantum VS server; and
there is an equivalent User record in Quantum VS.
If you log in as a different user, you will need to edit the username contained within the file addluckinsforwardprices.req - see Editing The User Name below.
2. Make sure that the file addluckinsforwardprices.req exists within /usr/bct/luckins by entering:
cd /usr/bct/luckins/
ls -l
Notes: (1) If you do not have this file please contact Quantum VS Professional Services on 01342 333807 or psg@edp.co.uk. (2) The contents of this file will be: <f5><f9>appstate<fd>addluckinsforwardprices<fe>env<fd>default<fe>authuserid<fd>{username}<f4> where <f5> , <f9>, <fd>, <fe>, and <f4> represent a single ASCII character equivalent to the hexadecimal code.
3. Run the following script:
export UVCBLENVVAR="`cat /usr/bct/luckins/addluckinsforwardprices.req`"
cobrun -F +B EDPGUI01 > EDPGUI01.rsp
Notes: (1) The correct path/environment variables must be set up on the Quantum VS server for this script to run. (2) The 'cat' statement is enclosed by a grave accent ASCII character and not a single quote ASCII character.
The response to the process is held within EDPGUI01.rsp.
4. You may now open a Product record in Quantum VS to check data has been updated. Select the Product Finder and use the Imported From Luckins filter to display standard Quantum VS Products which have been imported from the Luckins catalogue. See Using The Product Finder To View Luckins Products.
5. Any product price updates are added to the Forward Price file, rather than applied to the Product record directly. There is the option to manually review price updates before applying them. See Updating Product Prices And Costs.
The Apply Forward Prices process is used to apply the 'forward' price/cost updates to the product record. There is the option to specify to update prices to Luckins-linked Product records only, if required. See Apply Forward Prices.
The file addluckinsforwardprices.req is configured for the user 'op1unix'. If you wish to amend the username contained within the file then use vi -b addluckinsforwardprices.req. Be careful when editing so that you do not lose the special control-characters that separate fields. Delete single characters at a time and insert single characters at a time rather than attempting to change whole words.