Changes to PO_Main since 6.4.3

[SBarnes]

Hi Guys,

This question might test the memory to remember but I am in the process on upgrading a site on Jiwa 6.4.3 to Jiwa 7

In 6.4.3 the PO_Main table looks like

CREATE TABLE [dbo].[PO_Main](
[OrderID] [char](20) NOT NULL,
[LastSavedDateTime] [datetime] NULL,
[CreditorID] [char](20) NOT NULL,
[OrderNo] [varchar](15) NOT NULL,
[OrderedDate] [datetime] NOT NULL,
[Header1] [varchar](255) NULL,
[Header2] [varchar](255) NULL,
[Header3] [varchar](255) NULL,
[Header4] [varchar](255) NULL,
[Header5] [varchar](255) NULL,
[ContactType] [smallint] NOT NULL,
[Status] [smallint] NOT NULL,
[OrderType] [smallint] NULL,
[OrderTypeInvID] [char](20) NULL,
[OrderTypeInvDetails] [varchar](100) NULL,
[IN_LogicalID] [char](20) NOT NULL,
[OrderSupplierType] [smallint] NOT NULL,
[CentralWarehouseID] [char](20) NULL,
[TaxTotal] [float] NULL,
[CurrencyID] [char](20) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
and in Jiwa 7

CREATE TABLE [dbo].[PO_Main](
[OrderID] [char](20) NOT NULL,
[LastSavedDateTime] [datetime] NULL,
[CreditorID] [char](20) NOT NULL,
[OrderNo] [varchar](15) NOT NULL,
[OrderedDate] [datetime] NOT NULL,
[Header1] [varchar](255) NULL,
[Header2] [varchar](255) NULL,
[Header5] [varchar](255) NULL,
[Status] [smallint] NOT NULL,
[OrderType] [smallint] NULL,
[OrderTypeInvID] [char](20) NULL,
[IN_LogicalID] [char](20) NOT NULL,
[OrderSupplierType] [smallint] NOT NULL,
[CentralWarehouseID] [char](20) NULL,
[TaxTotal] [decimal](19, 6) NULL,
[CurrencyID] [char](36) NULL,
[InTransitWarehouseID] [char](20) NOT NULL DEFAULT (''),
[Reference] [varchar](50) NULL,
[PO_Workflows_RecID] [char](36) NOT NULL,
[HR_Staff_RecID] [char](20) NULL,
[CR_Warehouse_WarehouseID] [char](36) NULL,
CONSTRAINT [PK_PO_Main] PRIMARY KEY NONCLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

As can be seen fields Header3 and Header4 have disappeared in the process as has the data in them, the problem also is that the original customisation used these two fields as part of a reporting system, so my question is has the data been lost or moved in the upgrade process and what would be the best way of avoiding this and possibly other similar issues given the age of this database?

[Mike.Sheen]

As can be seen fields Header3 and Header4 have disappeared in the process as has the data in them, the problem also is that the original customisation used these two fields as part of a reporting system, so my question is has the data been lost or moved in the upgrade process and what would be the best way of avoiding this and possibly other similar issues given the age of this database?

Header1 and Header2 remain and should be renamed to reflect their actual use (Attention, contactBy) - perhaps in a later version
Header3 (which was the staff member first name) became HR_Staff_RecID - the existing data was mapped via the fname to the staffid (UPDATE PO_Main SET HR_Staff_RecID = (SELECT TOP 1 StaffID FROM HR_Staff WHERE Fname = Header3))
Header4 was not actually used by any standard functionality, so it was removed.
Header5 also is not used, but remains for some reason.

If you had data in PO_Main.Header3 which was not the staff member first name, or anything in PO_Main.Header4 then you'll need to script that into a temporary table prior to upgrade.

e.g.:

Code: Select all

CREATE TABLE TEMP_PO_Main_Header_Fields
(
   OrderID CHAR(20) NOT NULL,
   Header3 VARCHAR(255) NULL,
   Header4 VARCHAR(255) NULL
)
GO

INSERT INTO TEMP_PO_Main_Header_Fields
SELECT OrderID, Header3, Header4
FROM PO_Main

Then after upgrade create a plugin with 2 custom fields and perform an INSERT into PO_CustomSettingValues from the temp table.

Code: Select all

INSERT INTO PO_CustomSettingValues
SELLECT NewID(),
      (SELECT SettingID FROM PO_CustomSettings WHERE SettingName = 'Header3' AND PO_CustomSettings.SY_Plugin_RecID = (SELECT RecID FROM SY_Plugin WHERE Name = 'Purchase Order Header Fields')),
      OrderID,
      TEMP_PO_Main_Header_Fields.Header3,
      GETDATE()
FROM TEMP_PO_Main_Header_Fields

INSERT INTO PO_CustomSettingValues
SELLECT NewID(),
      (SELECT SettingID FROM PO_CustomSettings WHERE SettingName = 'Header4' AND PO_CustomSettings.SY_Plugin_RecID = (SELECT RecID FROM SY_Plugin WHERE Name = 'Purchase Order Header Fields')),
      OrderID,
      TEMP_PO_Main_Header_Fields.Header4,
      GETDATE()
FROM TEMP_PO_Main_Header_Fields

And then the reporting system needs to be modified to look at PO_CustomSettingValues instead. If that's not possible, you can always create a view called PO_Main which joins PO_Main with the TEMP_PO_Main_Header_Fields table populated above, returning the fields as their original names, Header3 and Header4 - and the reporting system should happily continue to work. You'll want some indexes on TEMP_PO_Main_Header_Fields if you plan on keeping it around, and you'd obviously probably want to name it differently.

Avoiding such issues is impossible - but as shown above there are ways to deal with the changes made over time.

[SBarnes]

Thanks Mike,

I think I'll need to get the client to take a look at this and other fields in the test database first very closely, this was actually the customer who you ran the upgrade against that David told me took over 40 hours to run.

Going from such an early version to 7 as opposed to 6.5.13 is obviously a fairly bigger leap that's going to need to be reviewed fairly closely by them before any go live, a look through the upgrade script database is obviously now going to need to be part of the whole process to avoid breaking anything major.

Thanks again.