rpsc_matrix_load
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
rpsc_matrix_load [2007/07/05 11:41 (17 years ago)] – cliff | rpsc_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 | + | 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, | + | * The first thing to be aware of is to export the Inventory from RPSC using PartNumbers, |
- | * 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 | + | * 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 |
* 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: | * **WARNING: | ||
* 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 | + | - Import the matrix files to System Five: This will add the matrix parents, update the Size fields on the child matrix parts, and attach |
- Fix part numbers in System Five to use the format SKU-PartNum, | - Fix part numbers in System Five to use the format SKU-PartNum, | ||
- | - Verify when done that the Padded Part number from System Five has been added to the Auxilliary | + | - Verify when done that the Padded Part number from System Five has been added to the Auxiliary |
+ | |||
+ | |||
+ | |||
+ | |||
Line 45: | Line 52: | ||
- Export **qry-Step02-A, | - Export **qry-Step02-A, | ||
- Place the **qry-Step03-A, | - Place the **qry-Step03-A, | ||
- | - Import **qry-Step02-A, | + | - Run a query on Inventry |
- | - **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, | - Import **qry-Step03-A, | ||
- | - Import **qry-Step03-B, | + | |
+ | - **WARNING: | ||
+ | - **Caution: | ||
+ | | ||
+ | - 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< | ||
+ | |||
+ | |||
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 | + | 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 |
* 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 | + | The Virtual Warehouse (Formerly known as the Auxiliary |
===== Queries used ===== | ===== Queries used ===== | ||
- | **Information: | + | **Information: |
+ | |||
==== qry RPSC Inventory ==== | ==== qry RPSC Inventory ==== | ||
< | < | ||
- | SELECT CStr([Part_Number]) AS Part_Number_NotPadded, | + | SELECT CStr([Part_Number]) AS Part_Number_NotPadded, |
+ | IMInventory.MATRIX_HEADER_ID, | ||
FROM IMInventory; | FROM IMInventory; | ||
</ | </ | ||
+ | |||
==== qry RPSC Inventory with Category ==== | ==== qry RPSC Inventory with Category ==== | ||
< | < | ||
SELECT Inventry.Cat, | SELECT Inventry.Cat, | ||
- | FROM [qry RPSC Inventory] INNER JOIN Inventry ON [qry RPSC Inventory].Part_Number_NotPadded = Inventry.Part; | + | FROM [qry RPSC Inventory] |
+ | INNER JOIN Inventry ON [qry RPSC Inventory].Part_Number_NotPadded = Inventry.Part; | ||
</ | </ | ||
Line 124: | Line 145: | ||
GROUP BY [qry RPSC Inventory with Category].SKU, | GROUP BY [qry RPSC Inventory with Category].SKU, | ||
</ | </ | ||
+ | |||
==== qry-Step03-A, | ==== qry-Step03-A, | ||
< | < | ||
- | SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku], | + | SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku], |
- | 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; | + | 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; | ||
</ | </ | ||
Line 140: | Line 165: | ||
ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID; | ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID; | ||
</ | </ | ||
+ | |||
==== qryInfo-Missing Matrix Children==== | ==== qryInfo-Missing Matrix Children==== | ||
< | < | ||
- | SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku], | + | SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku], |
- | 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 | + | 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)); | WHERE ((([qry RPSC Inventory with Category].Cat) Is Null)); | ||
</ | </ | ||
+ | |||
==== qryInfo-Missing Matrix Parents ==== | ==== qryInfo-Missing Matrix Parents ==== | ||
< | < | ||
SELECT IMMatrixHeader.Matrix_ID, | SELECT IMMatrixHeader.Matrix_ID, | ||
- | 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, | GROUP BY IMMatrixHeader.Matrix_ID, | ||
HAVING ((([qry RPSC Inventory with Category].Cat) Is Null)); | HAVING ((([qry RPSC Inventory with Category].Cat) Is Null)); |
rpsc_matrix_load.1183660880.txt.gz · Last modified: 2007/07/05 11:41 (17 years ago) by cliff