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

Leave a Reply