2014.8.29.1 Upgrade Steps


1) Execute the following SQL Script on the APS_Publish Database. This will add ClearAllHistoryData and ClearOldHistoryData Stored Procedures to the database.

/****** Object:  StoredProcedure [dbo].[ClearAllHistoryData]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE [dbo].[ClearAllHistoryData]
@PublishInventory bit,
@PublishCapacityIntervals bit
AS
BEGIN
IF @PublishInventory = 1
BEGIN
DELETE FROM JobProductSalesOrderDemands;
DELETE FROM JobProductForecastDemands;
DELETE FROM JobProductSafetyStockDemands;
DELETE FROM JobProductTransferOrderDemands;
DELETE FROM JobProductDeletedDemands;
DELETE FROM PurchaseToStockSalesOrderDemands;
DELETE FROM PurchaseToStockForecastDemands;
DELETE FROM PurchaseToStockSafetyStockDemands;
DELETE FROM PurchaseToStockTransferOrderDemands;
DELETE FROM PurchaseToStockDeletedDemands;
DELETE FROM JobActivityInventoryAdjustments;
DELETE FROM SalesOrderDistributionInventoryAdjustments;
DELETE FROM ForecastShipmentInventoryAdjustments;
DELETE FROM PurchaseToStockInventoryAdjustments;
DELETE FROM TransferOrderDistributionInventoryAdjustments;
DELETE FROM SalesOrderLineDistributions;
DELETE FROM SalesOrderLines;
DELETE FROM SalesOrders;
DELETE FROM ForecastShipments;
DELETE FROM Forecasts;
DELETE FROM TransferOrderDistributions;
DELETE FROM TransferOrders;
END

IF @PublishCapacityIntervals = 1
BEGIN
DELETE FROM CapacityIntervalResourceAssignments;
DELETE FROM CapacityIntervals;
DELETE FROM RecurringCapacityIntervalResourceAssignments;
DELETE FROM RecurringCapacityIntervalRecurrences;
DELETE FROM RecurringCapacityIntervals;
END

DELETE FROM JobPaths;
DELETE FROM JobOperationAttributes;
DELETE FROM JobMaterialSupplyingActivities;
DELETE FROM Capabilities;
DELETE FROM JobResourceBlockIntervals;
DELETE FROM JobResourceBlocks;
DELETE FROM JobActivities;
DELETE FROM JobProducts;
DELETE FROM JobMaterials;
DELETE FROM JobResourceCapabilities;
DELETE FROM JobResources;
DELETE FROM JobOperations;
DELETE FROM ManufacturingOrders;
DELETE FROM Jobs;
DELETE FROM ResourceCapabilities;
DELETE FROM Resources;
DELETE FROM Departments;
DELETE FROM Plants;
DELETE FROM PurchasesToStock;
DELETE FROM Inventories;
DELETE FROM Warehouses;
DELETE FROM Items;
DELETE FROM PlantWarehouses;
DELETE FROM Schedules;
DELETE FROM KPIs;
END


GO
/****** Object: StoredProcedure [dbo].[ClearOldHistoryData] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE [dbo].[ClearOldHistoryData]
@MaxScheduleDisplayDate datetime,
@MaxWhatIfDisplayDate datetime,
@ScenarioType nvarchar(50),
@ClearInventory bit,
@ClearInventoryAdjustments bit,
@ClearBlocks bit,
@ClearBlockIntervals bit,
@ClearCapacityIntervals bit,
@MaxHorizonDays int
AS
BEGIN
DELETE FROM Schedules WHERE PublishDate < @MaxScheduleDisplayDate;
DELETE FROM Schedules WHERE PublishDate < @MaxWhatIfDisplayDate AND ScenarioType = @ScenarioType;

--Delete objects that are not being saved in History, in reverse order, clearing dependent objects first.
IF @ClearInventory = 1
BEGIN
DELETE FROM JobProductSalesOrderDemands;
DELETE FROM JobProductForecastDemands;
DELETE FROM JobProductSafetyStockDemands;
DELETE FROM JobProductTransferOrderDemands;
DELETE FROM JobProductDeletedDemands;
DELETE FROM PurchaseToStockSalesOrderDemands;
DELETE FROM PurchaseToStockForecastDemands;
DELETE FROM PurchaseToStockSafetyStockDemands;
DELETE FROM PurchaseToStockTransferOrderDemands;
DELETE FROM PurchaseToStockDeletedDemands;
DELETE FROM JobActivityInventoryAdjustments;
DELETE FROM SalesOrderDistributionInventoryAdjustments;
DELETE FROM ForecastShipmentInventoryAdjustments;
DELETE FROM PurchaseToStockInventoryAdjustments;
DELETE FROM TransferOrderDistributionInventoryAdjustments;
DELETE FROM ForecastShipments;
DELETE FROM Forecasts;
DELETE FROM SalesOrderLineDistributions;
DELETE FROM SalesOrderLines;
DELETE FROM SalesOrders;
DELETE FROM TransferOrderDistributions;
DELETE FROM TransferOrders;
DELETE FROM Items;
DELETE FROM PlantWarehouses;
DELETE FROM Warehouses;
DELETE FROM JobProducts;
DELETE FROM JobMaterials;
DELETE FROM JobMaterialSupplyingActivities;
END
ELSE
IF @ClearInventoryAdjustments = 1
BEGIN
DELETE FROM InventoryAdjustments;
END

IF @ClearBlocks = 1
BEGIN
DELETE FROM JobResourceBlocks;
END
ELSE
IF @ClearBlockIntervals = 1
BEGIN
DELETE FROM JobResourceBlockIntervals;
END

IF @ClearCapacityIntervals = 1
BEGIN
DELETE FROM CapacityIntervalResourceAssignments;
DELETE FROM CapacityIntervals;
DELETE FROM RecurringCapacityIntervalResourceAssignments;
DELETE FROM RecurringCapacityIntervalRecurrences;
DELETE FROM RecurringCapacityIntervals;
END

--Delete Activities that are too far after the ClockDate. (ie. only want to keep the short term schedule)
DELETE FROM Jobs WHERE Scheduled = 1 AND ScheduledStartDateTime > (SELECT Max(Clock) From Schedules WHERE Schedules.PublishDate=Jobs.PublishDate) + @MaxHorizonDays;
DELETE FROM ManufacturingOrders WHERE Scheduled = 1 AND ScheduledStart > (SELECT Max(Clock) From Schedules WHERE Schedules.PublishDate=ManufacturingOrders.PublishDate) + @MaxHorizonDays;
DELETE FROM JobOperations WHERE Scheduled = 1 AND ScheduledStart > (SELECT Max(Clock) From Schedules WHERE Schedules.PublishDate=JobOperations.PublishDate) + @MaxHorizonDays;
DELETE FROM JobActivities WHERE Scheduled = 1 AND ScheduledStartDate > (SELECT Max(Clock) From Schedules WHERE Schedules.PublishDate=JobActivities.PublishDate) + @MaxHorizonDays;
DELETE FROM PurchasesToStock WHERE ScheduledReceiptDate > (SELECT Max(Clock) From Schedules WHERE Schedules.PublishDate=PurchasesToStock.PublishDate) + @MaxHorizonDays;

END

2) Re-Map CapabilityExternalID source field on “Required Capabilities Mappings” page of Data Mappings
requiredCapability