Windward Software Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== RPSC Matrix Data Load Procedures ======


===== What you need =====
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 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 attached properly to the supplier name.
  * 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.
  * 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.
  * 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.**







===== Overview / Goals =====
The goal is to
  - Do a normal import of parts from RPSC
  - Be able to view the RPSC data by referencing a padded 6 digit Part number
  - 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
  - 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. 
  - Verify when done that the Padded Part number from System Five has been added to the Auxiliary Supplier tab for all inventory imported.
















===== How to get things ready for import into System Five =====
  - Add or import module to give the ability to pad strings
  - Link to the Inventry table via ODBC. You can link to the DSN of this customers data. **Hint:** If you copy over just the Invent5.btr file you can replace the Invent5.btr file from a working demo in order to save on download time.
  - Use the query: **qry-Step01-A, getting newsku for IMInventory** to see these part numbers in a query
  - Use the query: **qry-Step02-A, Matrix Parents** to get the Matrix Parent part listing. We are looking at the SKU because the Matrix Parents don't have part numbers in the other system. Since all child parts of the Matrix Parents have the same SKU we use the SKU from the children.
  - Use the query: **qry-Step03-A, Matrix Children** to get the Matrix Children part listing including what the new updated SKU will look like (For later reference). This query may fail on the **newFormatSKU** field because the **Left** VBA function might not work in access at first. The problem is a missing library **Microsoft DAO 2.5/3.5 Compatibility Library**. If you go to references in the Visual Basic Editor you can select the **Microsoft DAO 3.6 Object Library** or other simularly named library. Go to **Tools>>References** to add a reference. 
  - **Note:** The length of a SKU in RPSC could be up to 15 characters, and if we add 7 characters for the Dash and formatted part number that would bring us to 22 character Part numbers. We only support 20 character part numbers in System Five.
  - 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.
  - 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>
  - Import **qry-Step03-A, Matrix Children.csv** which will set up the size fields which are used for the matrix setup
  - 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.























===== Pitfalls and things to watch =====
  * On virtually all imports 2 passes need to be made, when the second pass is made any editing of or creation of System Five inventory items will be lost and the new information will replace this.
  * Due to setup and fixes that need to be made after import some clients elect to only do 1 pass. This works as long as parts the inventory between the system is not changing. We can then update the quantities at go live time to get things in order. Any new items that have been added since the initial import would have to be manually entered after the fact.
  * 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.
  * 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 ===
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**
  * Use this formula to calculate the percentage of inventory items in stock: Stocked Items / Total Number of Items * 100
  * The number of stocked items is equal to the **CountOfStocked** value on the row where **Stocked** = -1
  * The total number of stocked items is equal to the sum of all **CountOfStocked** lines
  * If you export this query to excel you should be able to place the following formula in Cell B5: 
<code|X>=B3/SUM(B2:B4)*100&"%"</code>
== Extra Information ==
The **qryInfo-Percentage of inventory items in stock** query relies on the **qtyOnHand** query to exist unchanged in order to work. Cleanup of these extra parts can be done within system five using the Remove non moving stock Toolbox routine. This way you can remove anything that has not moved, and anything that has history would be marked for deletion. The reason this is a good and flexible way to do things is you can do a report on all items marked for deletion and if necessary turn that flag off with a data import (If showing a customer how to do this), or via a query.
<code|x>Update "Inventry" set markDeleted = 0 where markDeleted = 1</code>
I used the **where markDeleted = 1** part of the query to illustrate the filter that could be used if you wanted to run a SELECT query to see all items marked for deletion first. The above SQL statement doesn't need the where clause in order to work. Here is and example of a Select query to see all items currently marked for deletion: 
<code|x>Select * from "Inventry" where markDeleted = 1</code>

== What is the Virtual Warehouse used for? ==
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 =====
**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.


==== qry RPSC Inventory ====
<code|X>
SELECT CStr([Part_Number]) AS Part_Number_NotPadded, IMInventory.SKU, IMInventory.Description, 
 IMInventory.MATRIX_HEADER_ID, IMInventory.MATRIX_LAYER1_ID, IMInventory.MATRIX_LAYER2_ID, IMInventory.MATRIX_LAYER3_ID
FROM IMInventory;
</code>


==== qry RPSC Inventory with Category ====
<code|X>
SELECT Inventry.Cat, Inventry.InvUnique AS WWs5InvUnique, [qry RPSC Inventory].*
FROM [qry RPSC Inventory] 
INNER JOIN Inventry ON [qry RPSC Inventory].Part_Number_NotPadded = Inventry.Part;
</code>

==== qry-Step01-A, getting newsku for IMInventory ====
<code|X>
SELECT IMInventory.Part_Number, IMInventory.SKU, IMInventory.Description, IMInventory.Category_Code, pad([part_number],6,False) AS NewSku
FROM IMInventory;
</code>


==== qry-Step02-A, Matrix Parents ====
<code|X>
SELECT [qry RPSC Inventory with Category].SKU, [qry RPSC Inventory with Category].Cat, IMMatrixHeader.Matrix_Desc
FROM IMMatrixHeader INNER JOIN [qry RPSC Inventory with Category]
 ON IMMatrixHeader.Matrix_ID = [qry RPSC Inventory with Category].MATRIX_HEADER_ID
GROUP BY [qry RPSC Inventory with Category].SKU, [qry RPSC Inventory with Category].Cat, IMMatrixHeader.Matrix_Desc;
</code>


==== qry-Step03-A, Matrix Children ====
<code|X>
SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku],13) & "-" & pad([Part_Number_NotPadded],6,False) AS NewFormatSKU, 
IMMatrixLayer1.Item_Desc AS Size1, IMMatrixLayer2.Item_Desc AS Size2, [qry RPSC Inventory with Category].Cat
FROM ([qry RPSC Inventory with Category] INNER JOIN IMMatrixLayer1
 ON [qry RPSC Inventory with Category].MATRIX_LAYER1_ID = IMMatrixLayer1.Layer1_ID) INNER JOIN IMMatrixLayer2
 ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID;
</code>


==== qry-Step03-B, Matrix Children Kit load====
<code|X>
SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, [qry RPSC Inventory with Category].SKU, "0" AS KitQty
FROM ([qry RPSC Inventory with Category] INNER JOIN IMMatrixLayer1
 ON [qry RPSC Inventory with Category].MATRIX_LAYER1_ID = IMMatrixLayer1.Layer1_ID)
 INNER JOIN IMMatrixLayer2
 ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID;
</code>


==== qryInfo-Missing Matrix Children====
<code|X>
SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku],13) & "-" & pad([Part_Number_NotPadded],6,False) AS NewFormatSKU, 
IMMatrixLayer1.Item_Desc AS Size1, IMMatrixLayer2.Item_Desc AS Size2, [qry RPSC Inventory with Category].Cat
FROM ([qry RPSC Inventory with Category] INNER JOIN IMMatrixLayer1
 ON [qry RPSC Inventory with Category].MATRIX_LAYER1_ID = IMMatrixLayer1.Layer1_ID)
INNER JOIN IMMatrixLayer2 ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID
WHERE ((([qry RPSC Inventory with Category].Cat) Is Null));
</code>


==== qryInfo-Missing Matrix Parents ====
<code|X>
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
GROUP BY IMMatrixHeader.Matrix_ID, IMMatrixHeader.Matrix_Desc, [qry RPSC Inventory with Category].Cat
HAVING ((([qry RPSC Inventory with Category].Cat) Is Null));
</code>

==== qryInfo-Part Numbers that didn't get to WWS5 ====
<code|X>
SELECT [qry RPSC Inventory].*
FROM [qry RPSC Inventory] LEFT JOIN Inventry ON [qry RPSC Inventory].Part_Number_NotPadded = Inventry.Part
WHERE (((Inventry.Part) Is Null));
</code>

==== qryInfo-Percentage of inventory items in stock ====
<code|X>
SELECT qtyOnHand.Stocked, Count(qtyOnHand.Stocked) AS CountOfStocked
FROM IMInventory INNER JOIN qtyOnHand ON IMInventory.Part_Number = qtyOnHand.Part_Number
GROUP BY qtyOnHand.Stocked;
</code>

==== qtyOnHand ====
<code|X>
SELECT IMInventory.Part_Number, [qty_on_hand]<>0 AS Stocked
FROM IMInventory;
</code>