Windward Software Wiki

User Tools

Site Tools

Trace:
rpsc_matrix_load

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
rpsc_matrix_load [2007/07/05 11:43 (17 years ago)] – cliffrpsc_matrix_load [2016/04/14 11:25 (8 years ago)] (current) – [Pitfalls and things to watch] sjackson
Line 1: Line 1:
 ====== RPSC Matrix Data Load Procedures ====== ====== RPSC Matrix Data Load Procedures ======
 +
  
 ===== What you need ===== ===== What you need =====
-The overall goal is to import inventory from RPSC to System Five and then add matrix parents to SystemFive linking them to the matrix child parts. +The overall goal is to import inventory from RPSC to System Five and then add matrix parents to System Five linking them to the matrix child parts. 
-  * The first thing to be aware of is to export the Inventory from RPSC using PartNumbers, not SKU's in System Five. This is necessary for being able to data load matrix information without extra hastle. +  * The first thing to be aware of is to export the Inventory from RPSC using PartNumbers, not SKU's in System Five. This is necessary for being able to data load matrix information without extra hassle. 
-  * It is necessary to create a supplier and inventory export from RPSC. The Supplier import needs to be done so that the Inventory can be attatched properly to the supplier name.+  * It is necessary to create a supplier and inventory export from RPSC. The Supplier import needs to be done so that the Inventory can be attached properly to the supplier name.
   * The import of data to live system five needs to be complete. See warning below.   * The import of data to live system five needs to be complete. See warning below.
   * **WARNING:** You must choose to make RPSC Part Numbers your part numbers when asked during the RPSC Export process or it will not be possible to import your matrix items.   * **WARNING:** You must choose to make RPSC Part Numbers your part numbers when asked during the RPSC Export process or it will not be possible to import your matrix items.
   * You will need a copy of the Inventory.mdb database file from the RPSC data directory (in the ...\Store1\ directory)   * You will need a copy of the Inventory.mdb database file from the RPSC data directory (in the ...\Store1\ directory)
   * You will need Microsoft Access 2000 or higher installed on the machine you are doing the conversion on. (This machine should be on our side not the customers side). It is faster to download and work on the files here at the moment. If we were to get a one button macro running other options might be available that allow us to download an access database to the customers system.   * You will need Microsoft Access 2000 or higher installed on the machine you are doing the conversion on. (This machine should be on our side not the customers side). It is faster to download and work on the files here at the moment. If we were to get a one button macro running other options might be available that allow us to download an access database to the customers system.
 +  * During the data load process the **Sequential Part Numbering** Feature can not be enabled. There are more details mentioned in the Pitfalls section at the bottom of this page but the bottom lines is **it will break your system.**
  
  
Line 22: Line 24:
   - Make category codes for imported parts available by linking to ODBC data source for the clients live Windward System Five data.   - Make category codes for imported parts available by linking to ODBC data source for the clients live Windward System Five data.
   - Create kit part and matrix child import files from the RPSC database inventory.mdb   - Create kit part and matrix child import files from the RPSC database inventory.mdb
-  - Import the matrix files to System Five: This will add the matrix parents, update the Size fields on the child matrix parts, and attatch the child matrix items to their parents.+  - Import the matrix files to System Five: This will add the matrix parents, update the Size fields on the child matrix parts, and attach the child matrix items to their parents.
   - Fix part numbers in System Five to use the format SKU-PartNum, where the SKU is up to 13 characters followed by a dash and the 6 character padded PartNumber.    - Fix part numbers in System Five to use the format SKU-PartNum, where the SKU is up to 13 characters followed by a dash and the 6 character padded PartNumber. 
-  - Verify when done that the Padded Part number from System Five has been added to the Auxilliary Supplier tab for all inventory imported.+  - Verify when done that the Padded Part number from System Five has been added to the Auxiliary Supplier tab for all inventory imported. 
 + 
 + 
 + 
 + 
  
  
Line 45: Line 52:
   - Export **qry-Step02-A, Matrix Parents**, **qry-Step03-A, Matrix Children**, and **qry-Step03-B, Matrix Children Kit Load** to CSV including field names in the first row   - Export **qry-Step02-A, Matrix Parents**, **qry-Step03-A, Matrix Children**, and **qry-Step03-B, Matrix Children Kit Load** to CSV including field names in the first row
   - Place the **qry-Step03-A, Matrix Children.xml**, **qry-Step03-A, Matrix Children.xml**, and **qry-Step03-B, Matrix Children Kit Load.xml** files in with the associated csv files. This will speed up the import process and avoid mistakes because the settings will already be in the files.   - Place the **qry-Step03-A, Matrix Children.xml**, **qry-Step03-A, Matrix Children.xml**, and **qry-Step03-B, Matrix Children Kit Load.xml** files in with the associated csv files. This will speed up the import process and avoid mistakes because the settings will already be in the files.
-  - Import **qry-Step02-A, Matrix Parents.csv**, but make sure you save the results. We need to get the unique ID of the first Matrix Parent added for later use in a query to update Part Numbers +  - Run a query on Inventry to turn off kits. It seems that everything was loaded as kits. **Information:** The kit and kit2 tables were left blank.<code|x>update inventry set KitType = 0</code>
-  - **Caution:** We need to test PO's for matrix parent items working because I don't know if we will have a problem if the Supplier is not attatched to the matrix parent.+
   - Import **qry-Step03-A, Matrix Children.csv** which will set up the size fields which are used for the matrix setup   - Import **qry-Step03-A, Matrix Children.csv** which will set up the size fields which are used for the matrix setup
-  - Import **qry-Step03-B, Matrix Children Kit Load** via a kit load which will attatch the child parts to the parent parts.+  - Import **qry-Step02-A, Matrix Parents.csv**, but make sure you save the results. We need to get the unique ID of the first Matrix Parent added for later use in a query to update Part Numbers 
 +  - **WARNING:** I had a problem when first running this file because I ran it before the **qry-Step03-A, Matrix Children.csv** query. I was getting messages about the part already existing. I am trying to run things in this order to prevent this error. I have also Imported the new format sku into the item number field in System Five because I think this is the cause of the problem. 
 +  - **Caution:** We need to test PO's for matrix parent items working because I don't know if we will have a problem if the Supplier is not attached to the matrix parent. 
 +  - Import **qry-Step03-B, Matrix Children Kit Load** via a kit load which will attach the child parts to the parent parts. 
 +  - Since the Item Number is now updated to the NewSKU format, it does not have any size information in it. Also we need to check about the clients searching methods to make sure things work for them. All of the updates for these can be done afterwards.  
 +  - Behavior: The system will not pull up matrix children when you search for the main parent part. ex: VOLCSHIMCRANE01 will only bring up the parent part in the search window at this time. 
 +  - A query will need to be ran to update all Parts with a unique less than the first Parent Matrix Part Number<code>update Inventry set Part = Item where InvUnique < 10</code> Just make sure you replace 10 with the unique number of the first Parent Matrix Part Number you loaded. Remember there are lots of parts that were not matrix parts that still have only a part number as the primary search method. 
 + 
 + 
  
  
Line 74: Line 89:
   * Check to see if only the correct stock is being imported (Topic broken out at bottom of section)   * Check to see if only the correct stock is being imported (Topic broken out at bottom of section)
   * Check on Kit type for child items. It looks like the kit type on the child items was set to 1 (information only). I need to check this with John Daniels in programming to see if this is ok.   * Check on Kit type for child items. It looks like the kit type on the child items was set to 1 (information only). I need to check this with John Daniels in programming to see if this is ok.
-  * Parent kit items fail to load. Waiting for programming on fix for this.+  * Parent kit items fail to load. The problem was caused by the feature to use sequential part numbers. The part numbers added to System Five were sequential which is why I could not find them after they were loaded. And as a result each load doubled them up.
  
  
 === Check to see if only the correct stock is being imported === === Check to see if only the correct stock is being imported ===
-We use a couple queries to produce a count of items in stock, and compare this with the items that are not in stock (qty = 0). This is a good indicator to bring your attention to missed steps or improper information being added to system five. As a rule of thumb if the percentage items stocked is less than or equal to 50% of the total number of inventory being imported the data has not been cleaned. It may contain items that should be in the auxilliary database, or items that are no longer used and need to be removed. Here is a way to quickly check this figure:+We use a couple queries to produce a count of items in stock, and compare this with the items that are not in stock (qty = 0). This is a good indicator to bring your attention to missed steps or improper information being added to system five. As a rule of thumb if the percentage items stocked is less than or equal to 50% of the total number of inventory being imported the data has not been cleaned. It may contain items that should be in the auxiliary database, or items that are no longer used and need to be removed. Here is a way to quickly check this figure:
   * Run the query **qryInfo-Percentage of inventory items in stock**   * Run the query **qryInfo-Percentage of inventory items in stock**
   * Use this formula to calculate the percentage of inventory items in stock: Stocked Items / Total Number of Items * 100   * Use this formula to calculate the percentage of inventory items in stock: Stocked Items / Total Number of Items * 100
Line 92: Line 107:
  
 == What is the Virtual Warehouse used for? == == What is the Virtual Warehouse used for? ==
-The Virtual Warehouse (Formerly known as the Auxilliary database) is used as a price list made up of price lists from each of your suppliers. It is important to understand what the purpose of the Virtual Warehouse is so you can make sure parts that should be in the Virtual Warehouse are not placed into regular inventory. If a part is not normally stocked, and it is not a special order item and is not seasonal item then it should not be an inventory item. To rephrase: If a part is stocked, or is a special order item or is a seasonal item it should be an inventory item; if one of those is not true then it should be a Virtual Warehouse item/part.+The Virtual Warehouse (Formerly known as the Auxiliary database) is used as a price list made up of price lists from each of your suppliers. It is important to understand what the purpose of the Virtual Warehouse is so you can make sure parts that should be in the Virtual Warehouse are not placed into regular inventory. If a part is not normally stocked, and it is not a special order item and is not seasonal item then it should not be an inventory item. To rephrase: If a part is stocked, or is a special order item or is a seasonal item it should be an inventory item; if one of those is not true then it should be a Virtual Warehouse item/part. 
  
  
  
 ===== Queries used ===== ===== Queries used =====
-**Information:** Please note these are access queries and the syntax used will not likely work in Pervasive unless modified.+**Information:** Please note these are access queries and the syntax used will not likely work in Pervasive unless modified. Also there are some additional queries ran in pervasive, but they are noted in the section they are used in.
  
  
Line 160: Line 176:
 WHERE ((([qry RPSC Inventory with Category].Cat) Is Null)); WHERE ((([qry RPSC Inventory with Category].Cat) Is Null));
 </code> </code>
 +
  
 ==== qryInfo-Missing Matrix Parents ==== ==== qryInfo-Missing Matrix Parents ====
 <code|X> <code|X>
 SELECT IMMatrixHeader.Matrix_ID, IMMatrixHeader.Matrix_Desc, [qry RPSC Inventory with Category].Cat SELECT IMMatrixHeader.Matrix_ID, IMMatrixHeader.Matrix_Desc, [qry RPSC Inventory with Category].Cat
-FROM IMMatrixHeader LEFT JOIN [qry RPSC Inventory with Category] ON IMMatrixHeader.Matrix_ID = [qry RPSC Inventory with Category].MATRIX_HEADER_ID+FROM IMMatrixHeader LEFT JOIN [qry RPSC Inventory with Category] 
 + ON IMMatrixHeader.Matrix_ID = [qry RPSC Inventory with Category].MATRIX_HEADER_ID
 GROUP BY IMMatrixHeader.Matrix_ID, IMMatrixHeader.Matrix_Desc, [qry RPSC Inventory with Category].Cat GROUP BY IMMatrixHeader.Matrix_ID, IMMatrixHeader.Matrix_Desc, [qry RPSC Inventory with Category].Cat
 HAVING ((([qry RPSC Inventory with Category].Cat) Is Null)); HAVING ((([qry RPSC Inventory with Category].Cat) Is Null));
rpsc_matrix_load.1183661037.txt.gz · Last modified: 2007/07/05 11:43 (17 years ago) by cliff