Deleting inventory items prior to upg to 7 series

[DannyC]

I have had this query a few times before & just recently had it again.
For clients contemplating upgading from 6.xx to 7.xx many if them have obsolete and/or deleted inventory.

When they upgrade, they do not want these items to come across.
Whilst it is easy to DELETE FROM IN_Main where STATUS IN (2,4) I have a few questions prior to doing this

1. Would it be best to remove them from the table(s) prior to upgrading or after?
2. What would be the impact with regards to referential integrity, mainly for the transactional tables such as SO_Lines, QO_Lines, PO_Lines, WH_TransferLines, IN_StockTakeLines, IN_TransferLines and so on? Should I also delete the records from those tables? The client(s) wouldn't care because they'd be really old transactions from several years ago.
3. Obviously I would also need to delete from the following tables also.
IN_SellingPrices
IN_OnBackOrder
IN_DebtorSpecificPrices
IN_DebtorClassificationSpecificPrices
IN_Creditor
IN_SupplierWarehouse
IN_GroupLink
IN_Upsell
IN_OrderLevels

are there other tables I've missed?

Are there other consequences I should be aware of?

[Mike.Sheen]

1. Would it be best to remove them from the table(s) prior to upgrading or after?

After. Version 7 introduces many more integrity constraints in the database scheme to enforce foreign key relationships - so doing it before upgrade will mean the upgrade will potentially fail due to the introduction of these constraints.

2. What would be the impact with regards to referential integrity, mainly for the transactional tables such as SO_Lines, QO_Lines, PO_Lines, WH_TransferLines, IN_StockTakeLines, IN_TransferLines and so on? Should I also delete the records from those tables? The client(s) wouldn't care because they'd be really old transactions from several years ago.

You won't have a choice but to delete the relational data before removing the item - there are many foreign key constraints which won't let you delete from IN_Main until the FK constraints are satisfied.

3. Obviously I would also need to delete from the following tables also.
IN_SellingPrices
IN_OnBackOrder
IN_DebtorSpecificPrices
IN_DebtorClassificationSpecificPrices
IN_Creditor
IN_SupplierWarehouse
IN_GroupLink
IN_Upsell
IN_OrderLevels

are there other tables I've missed?

doing an sp_help on IN_Main on a version 7 database shows the list of tables referenced by foreign key:

Code: Select all

BM_Inputs
BM_Outputs
BM_WorkOrderInputs
BM_WorkOrderOutputs
IN_AttributeGroup
IN_BinLocation
IN_Budget
IN_Creditor
IN_CustomSettingValues
IN_DebtorClassificationSpecificPrice
IN_DebtorPartNumbers
IN_DebtorPricingGroupSpecificPrice
IN_DebtorSpecificPrice
IN_Description
IN_LogicalOrder
IN_OnBackOrder
IN_OrderLevels
IN_PriceLink
IN_ProductAvailability
IN_RegionSupplierOrdering
IN_SellingPrices
IN_SOH
IN_StyleColourSizeMatrix
IN_SupplierWarehouse
IN_UnitOfMeasure
IN_WarehouseSOH
JB_EstimateFinishedGoodLines
JB_EstimateInventoryLines
PO_BatchPurchaseOrderLines
QO_Lines
RA_CreditExchangeLines
RA_CreditLines
RA_RequestLines
RA_ShipLines
SM_Labour
SM_Parts
SO_Lines


Are there other consequences I should be aware of?

Possibly - I'm sure those will present themselves pretty quickly. Then it's just a matter of scripts to update or delete rows as required.

[DannyC]

Awesome Mike, thanks for the advice.

[SBarnes]

HI Mike,

Further to Danny's question above, but on a slightly different tangent one of the things that we continually run into in upgrading a database to Jiwa 7 from Jiwa 6 is that the application of the foreign key constraints can fall over in the upgrade process due to the data in the tables, would it be possible to provide some sort of pre-check script with each release (even stored procedure to be added to a version 6 db) that would list the data from the tables where this would be a problem so it could be fixed ahead of the upgrade process?

[Scott.Pearce]

The best approach is to simply run the upgrade on a copy of the data and deal with any errors as they occur. What we do is create a "pre-upgrade script" that deals with any issues that came up during the test upgrade. This script can then be run *before* upgrading the live data, thus avoiding any data issues.

[Mike.Sheen]

What we do is create a "pre-upgrade script" that deals with any issues that came up during the test upgrade. This script can then be run *before* upgrading the live data, thus avoiding any data issues.

It should also be noted that when an issue is brought to our attention where upgrades fail due to such things as foreign key constraints - we do identify the issue and adjust the upgrade scripts retrospectively to make a best effort on how to handle that.

So, an upgrade to 7.00.157.00 may have failed when that version was first released, but we've since come across issues and altered the upgrade scripts so a database upgrading to 7.00.175.00 today which may have faltered previously on upgrade to 7.00.157.00 won't have the same issue.

[SBarnes]

Hi Mike & Scott

Firstly thanks for the responses, Mike would it be possible for the scripts db to be posted along side each release so that it's possible to grab the latest copy of the access database for use on a machine that may already have that release of Jiwa installed on it?

[Scott.Pearce]

would it be possible for the scripts db to be posted along side each release

No. Instead you should do this:

1. Download the desired version of setup.exe from http://support.jiwa.com.au/Home/Downloads

2. From admin cmd line execute the command:

Code: Select all

setup.exe /extract

3. In the resultant files you should see "disk1.cab", use something like WinRar to extract "JiwaDatabaseScripts.mdb" from it.

4. Opening JiwaDatabaseScripts.mdb with Access will reveal a query called "qryUpgrade" - look in here to see the sql upgrade scripts ordered by version and run order. An upgrade will go through these, followed by the contents of qryAlwaysRun.

[Mike.Sheen]

JiwaDatabaseScripts.mdb

Fun Fact: we introduced upgrades handled by the application back in 1997/1998 - prior to that we instead shipped the application with SQL Scripts (on floppy disks!) which had to be run in order.

The SQL scripts were named as per the version - 3.01.sql, 3.02.sql, 3.50.sql, et cetera.

Often people would run the scripts out of order, or scripts would fail but they would continue onto the next script ignoring the failure. Some impatient people even ran multiple scripts concurrently.

The ensuing mess was fun and exciting to clean up.

We've come a long way since then - but we really should move away from an MS Access database holding the scripts.

[SBarnes]

Hi Guys,

Once again thanks for the quick reply, I didn't know there were any command line switches for the Setup until Scott's post.

Mike's fun fact doesn't sound like much fun

As for the use of an access database it does have the advantage that if an upgrade fails you can get access to the offending SQL script to work on creating a work around.

The fact that an upgrade also resumes rather than starts over is also great in terms of time and determining whether or not you've been able to fix the problem.

My only other comment and I don't know how hard this would be to do is is there any advantage in the upgrade giving feedback in some format to you guys when it fails, like a Send results to Jiwa so that you could identify common problems more easily?