Joseph Doherty
26ff8d9b4f
Set up repository with legacy .NET Framework 4.8 source (OLD/), new .NET 10 Blazor solution (NEW/), OpenSpec specifications, documentation, and project configuration.
1049 lines
30 KiB
Transact-SQL
Executable File
1049 lines
30 KiB
Transact-SQL
Executable File
GO
|
|
PRINT N'Creating [dbo].[Branch]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.Branch') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[Branch];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[Branch] (
|
|
[Code] VARCHAR (12) NOT NULL,
|
|
[Description] VARCHAR (40) NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ([Code] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[Branch].[IX_Branch_Lookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_Branch_Lookup]
|
|
ON [dbo].[Branch]([Description] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[DataUpdate]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.DataUpdate') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[DataUpdate];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[DataUpdate] (
|
|
[ID] INT IDENTITY (1, 1) NOT NULL,
|
|
[SourceSystem] VARCHAR (50) NOT NULL,
|
|
[SourceData] VARCHAR (50) NOT NULL,
|
|
[TableName] VARCHAR (50) NOT NULL,
|
|
[StartDT] DATETIME NOT NULL,
|
|
[EndDT] DATETIME NOT NULL,
|
|
[UpdateType] SMALLINT NOT NULL,
|
|
[WasSuccessful] BIT NOT NULL,
|
|
[NumberRecords] BIGINT NOT NULL,
|
|
CONSTRAINT [PK_DataUpdate] PRIMARY KEY CLUSTERED ([ID] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[DataUpdate].[IX_DataUpdate_LastUpdate]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_DataUpdate_LastUpdate]
|
|
ON [dbo].[DataUpdate]([TableName] ASC, [WasSuccessful] ASC, [StartDT] DESC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[Item]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.Item') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[Item];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[Item] (
|
|
[ShortItemNumber] BIGINT NOT NULL,
|
|
[ItemNumber] VARCHAR (25) NOT NULL,
|
|
[Description] VARCHAR (30) NULL,
|
|
[PlanningFamily] VARCHAR (3) NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED ([ShortItemNumber] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[Item].[IX_Item_ItemNumber]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_Item_ItemNumber]
|
|
ON [dbo].[Item]([ItemNumber] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[JdeUser]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.JdeUser') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[JdeUser];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[JdeUser] (
|
|
[AddressNumber] BIGINT NOT NULL,
|
|
[UserID] VARCHAR (10) NULL,
|
|
[FullName] VARCHAR (40) NOT NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_JdeUser] PRIMARY KEY CLUSTERED ([AddressNumber] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[JdeUser].[IX_JdeUser_UserID]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_JdeUser_UserID]
|
|
ON [dbo].[JdeUser]([UserID] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[JdeUser].[IX_JdeUser_FullName]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_JdeUser_FullName]
|
|
ON [dbo].[JdeUser]([FullName] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[Lot]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.Lot') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[Lot];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[Lot] (
|
|
[LotNumber] VARCHAR (30) NOT NULL,
|
|
[BranchCode] VARCHAR (12) NOT NULL,
|
|
[ShortItemNumber] BIGINT NOT NULL,
|
|
[ItemNumber] VARCHAR (25) NULL,
|
|
[SupplierCode] BIGINT NOT NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_Lot] PRIMARY KEY CLUSTERED ([LotNumber] ASC, [ShortItemNumber] ASC, [BranchCode] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[Lot].[IX_Lot_SupplierCode]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_Lot_SupplierCode]
|
|
ON [dbo].[Lot]([SupplierCode] ASC)
|
|
INCLUDE([LotNumber]);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[LotUsage]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.LotUsage') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[LotUsage];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[LotUsage] (
|
|
[UniqueID] BIGINT NOT NULL,
|
|
[WorkOrderNumber] BIGINT NOT NULL,
|
|
[LotNumber] VARCHAR (30) NOT NULL,
|
|
[BranchCode] VARCHAR (12) NULL,
|
|
[ShortItemNumber] BIGINT NOT NULL,
|
|
[Quantity] DECIMAL (15, 2) NOT NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_LotUsage] PRIMARY KEY CLUSTERED ([UniqueID] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[LotUsage].[IX_LotUsage_WorkOrderNumber]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_LotUsage_WorkOrderNumber]
|
|
ON [dbo].[LotUsage]([WorkOrderNumber] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[LotUsage].[IX_LotUsage_LotLookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_LotUsage_LotLookup]
|
|
ON [dbo].[LotUsage]([LotNumber] ASC, [ShortItemNumber] ASC, [BranchCode] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[MisData]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.MisData') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[MisData];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[MisData] (
|
|
[ItemNumber] VARCHAR (32) NOT NULL,
|
|
[BranchCode] VARCHAR (32) NOT NULL,
|
|
[SequenceNumber] VARCHAR (32) NOT NULL,
|
|
[MisNumber] VARCHAR (32) NOT NULL,
|
|
[RevID] VARCHAR (32) NOT NULL,
|
|
[CharNumber] VARCHAR (32) NOT NULL,
|
|
[TestDescription] VARCHAR (2000) NULL,
|
|
[SamplingType] VARCHAR (32) NULL,
|
|
[SamplingValue] VARCHAR (32) NULL,
|
|
[ToolsGauges] VARCHAR (2000) NULL,
|
|
[WorkInstructions] VARCHAR (2000) NULL,
|
|
[Status] VARCHAR (32) NOT NULL,
|
|
[ReleaseDate] DATETIME NULL,
|
|
[ObsoleteDate] DATETIME NULL,
|
|
CONSTRAINT [PK_MisData] PRIMARY KEY CLUSTERED ([ItemNumber] ASC, [BranchCode] ASC, [SequenceNumber] ASC, [MisNumber] ASC, [RevID] ASC, [Status] ASC, [CharNumber] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[OrgHierarchy]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.OrgHierarchy') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[OrgHierarchy];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[OrgHierarchy] (
|
|
[WorkCenterCode] VARCHAR (12) NOT NULL,
|
|
[BranchCode] VARCHAR (12) NOT NULL,
|
|
[ProfitCenterCode] VARCHAR (12) NOT NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_OrgHierarchy] PRIMARY KEY CLUSTERED ([WorkCenterCode] ASC, [BranchCode] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[OrgHierarchy].[IX_OrgHierarchy_ProfitCenterCode]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_OrgHierarchy_ProfitCenterCode]
|
|
ON [dbo].[OrgHierarchy]([ProfitCenterCode] ASC)
|
|
INCLUDE([WorkCenterCode]);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[ProfitCenter]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.ProfitCenter') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[ProfitCenter];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[ProfitCenter] (
|
|
[Code] VARCHAR (12) NOT NULL,
|
|
[Description] VARCHAR (40) NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_ProfitCenter] PRIMARY KEY CLUSTERED ([Code] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[ProfitCenter].[IX_ProfitCenter_Lookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_ProfitCenter_Lookup]
|
|
ON [dbo].[ProfitCenter]([Description] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[RouteMaster]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.RouteMaster') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[RouteMaster];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[RouteMaster] (
|
|
[BranchCode] VARCHAR (12) NOT NULL,
|
|
[ItemNumber] VARCHAR (25) NOT NULL,
|
|
[RoutingType] VARCHAR (3) NOT NULL,
|
|
[SequenceNumber] DECIMAL (7, 2) NOT NULL,
|
|
[FunctionCode] VARCHAR (15) NULL,
|
|
[WorkCenterCode] VARCHAR (12) NULL,
|
|
[StartDate] DATETIME NOT NULL,
|
|
[EndDate] DATETIME NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_RouteMaster] PRIMARY KEY CLUSTERED ([BranchCode] ASC, [ItemNumber] ASC, [RoutingType] ASC, [SequenceNumber] ASC, [StartDate] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[Search]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.Search') IS NULL
|
|
BEGIN
|
|
CREATE TABLE [dbo].[Search] (
|
|
[ID] INT IDENTITY (1, 1) NOT NULL,
|
|
[UserName] VARCHAR (128) NOT NULL,
|
|
[Name] VARCHAR (128) NULL,
|
|
[Status] SMALLINT NOT NULL,
|
|
[SubmitDT] DATETIME NULL,
|
|
[StartDT] DATETIME NULL,
|
|
[EndDT] DATETIME NULL,
|
|
[Criteria] VARCHAR (MAX) NULL,
|
|
[Results] VARBINARY (MAX) NULL,
|
|
CONSTRAINT [PK_Search] PRIMARY KEY CLUSTERED ([ID] ASC)
|
|
);
|
|
|
|
PRINT N'Creating [dbo].[Search].[IX_Search_UserName]...';
|
|
|
|
CREATE NONCLUSTERED INDEX [IX_Search_UserName]
|
|
ON [dbo].[Search]([UserName] ASC);
|
|
END
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[StatusCode]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.StatusCode') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[StatusCode];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[StatusCode] (
|
|
[Code] VARCHAR (12) NOT NULL,
|
|
[Description] VARCHAR (40) NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_StatusCode] PRIMARY KEY CLUSTERED ([Code] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[StatusCode].[IX_StatusCode_Lookup]...';
|
|
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_StatusCode_Lookup]
|
|
ON [dbo].[StatusCode]([Description] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkCenter]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkCenter') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[WorkCenter];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[WorkCenter] (
|
|
[Code] VARCHAR (12) NOT NULL,
|
|
[Description] VARCHAR (40) NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_WorkCenter] PRIMARY KEY CLUSTERED ([Code] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkCenter].[IX_WorkCenter_Lookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkCenter_Lookup]
|
|
ON [dbo].[WorkCenter]([Description] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrder]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkOrder') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[WorkOrder];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[WorkOrder] (
|
|
[WorkOrderNumber] BIGINT NOT NULL,
|
|
[BranchCode] VARCHAR (12) NULL,
|
|
[LotNumber] VARCHAR (30) NULL,
|
|
[ItemNumber] VARCHAR (25) NULL,
|
|
[ShortItemNumber] BIGINT NOT NULL,
|
|
[ParentWorkOrderNumber] VARCHAR (8) NULL,
|
|
[OrderQuantity] DECIMAL (15, 2) NOT NULL,
|
|
[HeldQuantity] DECIMAL (15, 2) NOT NULL,
|
|
[ShippedQuantity] DECIMAL (15, 2) NOT NULL,
|
|
[StatusCode] VARCHAR (10) NULL,
|
|
[StatusCodeUpdateDT] DATETIME NULL,
|
|
[IssueDate] DATETIME NOT NULL,
|
|
[StartDate] DATETIME NOT NULL,
|
|
[RoutingType] VARCHAR (3) NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_WorkOrder] PRIMARY KEY CLUSTERED ([WorkOrderNumber] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrder].[IX_WorkOrder_ParentWorkOrderNumber]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrder_ParentWorkOrderNumber]
|
|
ON [dbo].[WorkOrder]([ParentWorkOrderNumber] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrder].[IX_WorkOrder_ItemNumber]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrder_ItemNumber]
|
|
ON [dbo].[WorkOrder]([ItemNumber] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderComponent]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkOrderComponent') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[WorkOrderComponent];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[WorkOrderComponent] (
|
|
[UniqueID] BIGINT NOT NULL,
|
|
[WorkOrderNumber] BIGINT NOT NULL,
|
|
[LotNumber] VARCHAR (30) NOT NULL,
|
|
[BranchCode] VARCHAR (12) NULL,
|
|
[ShortItemNumber] BIGINT NOT NULL,
|
|
[Quantity] DECIMAL (15, 2) NOT NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_WorkOrderComponent] PRIMARY KEY CLUSTERED ([UniqueID] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderComponent].[IX_WorkOrderComponent_WorkOrderNumber]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrderComponent_WorkOrderNumber]
|
|
ON [dbo].[WorkOrderComponent]([WorkOrderNumber] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderComponent].[IX_WorkOrderComponent_LotLookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrderComponent_LotLookup]
|
|
ON [dbo].[WorkOrderComponent]([LotNumber] ASC, [ShortItemNumber] ASC, [BranchCode] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderRouting]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkOrderRouting') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[WorkOrderRouting];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[WorkOrderRouting] (
|
|
[UserID] VARCHAR (40) NOT NULL,
|
|
[BatchNumber] VARCHAR (60) NOT NULL,
|
|
[TransactionNumber] VARCHAR (88) NOT NULL,
|
|
[LineNumber] INT NOT NULL,
|
|
[StepNumber] DECIMAL (7, 2) NOT NULL,
|
|
[WorkCenterCode] VARCHAR (12) NOT NULL,
|
|
[WorkOrderNumber] BIGINT NOT NULL,
|
|
[RoutingType] VARCHAR (12) NULL,
|
|
[BranchCode] VARCHAR (12) NULL,
|
|
[StepDescription] VARCHAR (120) NULL,
|
|
[FunctionCode] VARCHAR (60) NULL,
|
|
[TransactionDate] DATETIME NOT NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_WorkOrderRouting] PRIMARY KEY CLUSTERED ([UserID] ASC, [BatchNumber] ASC, [TransactionNumber] ASC, [LineNumber] ASC, [StepNumber] ASC, [WorkCenterCode] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderRouting].[IX_WorkOrderRouting_Lookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrderRouting_Lookup]
|
|
ON [dbo].[WorkOrderRouting]([WorkOrderNumber] ASC, [WorkCenterCode] ASC, [FunctionCode] ASC)
|
|
INCLUDE([StepNumber]);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderScrap]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkOrderScrap') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[WorkOrderScrap];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[WorkOrderScrap] (
|
|
[WorkOrderNumber] BIGINT NOT NULL,
|
|
[StepNumber] DECIMAL (7, 2) NOT NULL,
|
|
[StepTypeCode] VARCHAR (2) NOT NULL,
|
|
[WorkCenterCode] VARCHAR (12) NOT NULL,
|
|
[ScrappedQuantity] DECIMAL (17, 2) NOT NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_WorkOrderScrap] PRIMARY KEY CLUSTERED ([WorkOrderNumber] ASC, [StepNumber] ASC, [StepTypeCode] ASC, [WorkCenterCode] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderStep]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkOrderStep') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[WorkOrderStep];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[WorkOrderStep] (
|
|
[WorkOrderNumber] BIGINT NOT NULL,
|
|
[WorkCenterCode] VARCHAR (12) NOT NULL,
|
|
[StepNumber] DECIMAL (7, 2) NOT NULL,
|
|
[StepTypeCode] VARCHAR (2) NOT NULL,
|
|
[BranchCode] VARCHAR (12) NOT NULL,
|
|
[StepDescription] VARCHAR (30) NULL,
|
|
[FunctionOperationDescription] VARCHAR (80) NULL,
|
|
[StartDT] DATETIME NULL,
|
|
[EndDT] DATETIME NULL,
|
|
[FunctionCode] VARCHAR (15) NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_WorkOrderStep] PRIMARY KEY CLUSTERED ([WorkOrderNumber] ASC, [WorkCenterCode] ASC, [StepNumber] ASC, [StepTypeCode] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderStep].[IX_WorkOrderStep_WorkCenterCode]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrderStep_WorkCenterCode]
|
|
ON [dbo].[WorkOrderStep]([WorkCenterCode] ASC)
|
|
INCLUDE([WorkOrderNumber]);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderStep].[IX_WorkOrderStep_Lookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrderStep_Lookup]
|
|
ON [dbo].[WorkOrderStep]([EndDT] ASC)
|
|
INCLUDE([WorkOrderNumber], [StepNumber], [WorkCenterCode], [FunctionCode], [FunctionOperationDescription]);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderTime]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkOrderTime') IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE [dbo].[WorkOrderTime];
|
|
END
|
|
|
|
GO
|
|
CREATE TABLE [dbo].[WorkOrderTime] (
|
|
[UniqueID] BIGINT NOT NULL,
|
|
[WorkOrderNumber] BIGINT NOT NULL,
|
|
[StepNumber] DECIMAL (7, 2) NOT NULL,
|
|
[WorkCenterCode] VARCHAR (12) NOT NULL,
|
|
[BranchCode] VARCHAR (12) NOT NULL,
|
|
[AddressNumber] BIGINT NOT NULL,
|
|
[GlDate] DATETIME NULL,
|
|
[LastUpdateDT] DATETIME NOT NULL,
|
|
CONSTRAINT [PK_WorkOrderTime] PRIMARY KEY CLUSTERED ([UniqueID] ASC)
|
|
);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderTime].[IX_WorkOrderTime_Lookup]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrderTime_Lookup]
|
|
ON [dbo].[WorkOrderTime]([WorkOrderNumber] ASC, [WorkCenterCode] ASC, [StepNumber] ASC);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderTime].[IX_WorkOrderTime_AddressNumber]...';
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [IX_WorkOrderTime_AddressNumber]
|
|
ON [dbo].[WorkOrderTime]([AddressNumber] ASC)
|
|
INCLUDE([WorkOrderNumber], [WorkCenterCode], [StepNumber], [LastUpdateDT]);
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[MatchMIS]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.MatchMIS') IS NOT NULL
|
|
BEGIN
|
|
DROP FUNCTION [dbo].[MatchMIS];
|
|
END
|
|
|
|
GO
|
|
CREATE FUNCTION [dbo].[MatchMIS] (
|
|
@workOrderNumber BIGINT,
|
|
@itemNumber VARCHAR(25),
|
|
@branchCode VARCHAR(12),
|
|
@routingType VARCHAR(3),
|
|
@issueDate DATETIME,
|
|
@workCenterCode VARCHAR(12),
|
|
@sequenceNumber DECIMAL(7, 2),
|
|
@steptimestamp DATETIME,
|
|
@functionCode VARCHAR(15),
|
|
@functionOperationDescription VARCHAR(80)
|
|
)
|
|
RETURNS @MIS TABLE
|
|
(
|
|
WorkOrderNumber BIGINT,
|
|
ItemNumber VARCHAR(25),
|
|
ItemDescription VARCHAR(30),
|
|
BranchCode VARCHAR(12),
|
|
WorkCenterCode VARCHAR(12),
|
|
StepTimestamp DATETIME,
|
|
SequenceNumber DECIMAL(7, 2),
|
|
FunctionCode VARCHAR(15),
|
|
FunctionOperationDescription VARCHAR(80),
|
|
MatchedSequenceNumber DECIMAL(7, 2),
|
|
RoutingMatch BIT,
|
|
MasterMatch BIT,
|
|
MisNumber VARCHAR(32),
|
|
RevID VARCHAR(32),
|
|
CharNumber VARCHAR(32),
|
|
MisSequenceNumber VARCHAR(32),
|
|
TestDescription VARCHAR(2000),
|
|
SamplingType VARCHAR(32),
|
|
SamplingValue VARCHAR(32),
|
|
ToolsGauges VARCHAR(2000),
|
|
WorkInstructions VARCHAR(2000),
|
|
Status VARCHAR(32),
|
|
ReleaseDate DATETIME
|
|
)
|
|
BEGIN
|
|
--Lookup item description
|
|
DECLARE @itemDescription VARCHAR(30);
|
|
SELECT TOP 1 @itemDescription = i.Description FROM dbo.Item AS i WHERE i.ItemNumber = @itemNumber;
|
|
|
|
--Find aliases for sequence number
|
|
DECLARE @alias TABLE
|
|
(
|
|
SequenceNumber DECIMAL(7, 2),
|
|
RoutingMatch BIT,
|
|
MasterMatch BIT
|
|
);
|
|
|
|
--Lookup parent work order info
|
|
DECLARE @p_WorkOrderNumber BIGINT;
|
|
SELECT @p_WorkOrderNumber = COALESCE(TRY_CONVERT(BIGINT, wo.ParentWorkOrderNumber), wo.WorkOrderNumber) FROM dbo.WorkOrder AS wo WHERE wo.WorkOrderNumber = @workOrderNumber;
|
|
|
|
--Add F3112Z1 records to aliases
|
|
WITH routing_cte AS (
|
|
SELECT DISTINCT wor.StepNumber AS SequenceNumber
|
|
FROM dbo.WorkOrderRouting AS wor
|
|
WHERE wor.WorkOrderNumber = @p_WorkOrderNumber AND
|
|
wor.WorkCenterCode = @workCenterCode AND
|
|
wor.FunctionCode = @functionCode AND
|
|
wor.TransactionDate = (SELECT MIN(wor2.TransactionDate) FROM dbo.WorkOrderRouting AS wor2 WHERE wor2.WorkOrderNumber = @p_WorkOrderNumber)
|
|
)
|
|
MERGE @alias AS TARGET
|
|
USING routing_cte AS SOURCE ON (TARGET.SequenceNumber = SOURCE.SequenceNumber)
|
|
WHEN MATCHED THEN
|
|
UPDATE SET TARGET.RoutingMatch = 1
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT(SequenceNumber, RoutingMatch, MasterMatch)
|
|
VALUES (SOURCE.SequenceNumber, 1, 0);
|
|
|
|
--Add F3003 records to aliases
|
|
IF(NOT EXISTS(SELECT * FROM @alias AS a)) BEGIN
|
|
WITH master_cte AS
|
|
(
|
|
SELECT DISTINCT rm.SequenceNumber
|
|
FROM dbo.RouteMaster AS rm
|
|
WHERE rm.BranchCode = @branchCode AND
|
|
rm.ItemNumber = @itemNumber AND
|
|
rm.RoutingType = @routingType AND
|
|
@issueDate BETWEEN rm.StartDate AND rm.EndDate AND
|
|
rm.WorkCenterCode = @workCenterCode AND
|
|
rm.FunctionCode = @functionCode
|
|
)
|
|
MERGE @alias AS TARGET
|
|
USING master_cte AS SOURCE ON (TARGET.SequenceNumber = SOURCE.SequenceNumber)
|
|
WHEN MATCHED THEN
|
|
UPDATE SET TARGET.MasterMatch = 1
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT(SequenceNumber, RoutingMatch, MasterMatch)
|
|
VALUES(SOURCE.SequenceNumber, 0, 1);
|
|
END
|
|
ELSE BEGIN
|
|
WITH master_cte AS
|
|
(
|
|
SELECT DISTINCT rm.SequenceNumber
|
|
FROM dbo.RouteMaster AS rm
|
|
WHERE rm.BranchCode = @branchCode AND
|
|
rm.ItemNumber = @itemNumber AND
|
|
rm.RoutingType = @routingType AND
|
|
@issueDate BETWEEN rm.StartDate AND rm.EndDate AND
|
|
rm.WorkCenterCode = @workCenterCode AND
|
|
rm.FunctionCode = @functionCode
|
|
)
|
|
MERGE @alias AS TARGET
|
|
USING master_cte AS SOURCE ON (TARGET.SequenceNumber = SOURCE.SequenceNumber)
|
|
WHEN MATCHED THEN
|
|
UPDATE SET TARGET.MasterMatch = 1;
|
|
END
|
|
|
|
--Try to match to 'Current'
|
|
INSERT INTO @MIS
|
|
(
|
|
WorkOrderNumber,
|
|
ItemNumber,
|
|
ItemDescription,
|
|
BranchCode,
|
|
WorkCenterCode,
|
|
StepTimestamp,
|
|
SequenceNumber,
|
|
FunctionCode,
|
|
FunctionOperationDescription,
|
|
MatchedSequenceNumber,
|
|
RoutingMatch,
|
|
MasterMatch,
|
|
MisNumber,
|
|
RevID,
|
|
CharNumber,
|
|
MisSequenceNumber,
|
|
TestDescription,
|
|
SamplingType,
|
|
SamplingValue,
|
|
ToolsGauges,
|
|
WorkInstructions,
|
|
Status,
|
|
ReleaseDate
|
|
)
|
|
SELECT @workOrderNumber,
|
|
@itemNumber,
|
|
@itemDescription,
|
|
@branchCode,
|
|
@workCenterCode,
|
|
@steptimestamp,
|
|
@sequenceNumber,
|
|
@functionCode,
|
|
@functionOperationDescription,
|
|
a.SequenceNumber AS MatchedSequenceNumber,
|
|
a.RoutingMatch,
|
|
a.MasterMatch,
|
|
md.MisNumber,
|
|
md.RevID,
|
|
md.CharNumber,
|
|
md.SequenceNumber AS MisSequenceNumber,
|
|
md.TestDescription,
|
|
md.SamplingType,
|
|
md.SamplingValue,
|
|
md.ToolsGauges,
|
|
md.WorkInstructions,
|
|
md.Status,
|
|
md.ReleaseDate
|
|
FROM @alias AS a INNER JOIN
|
|
dbo.MisData AS md ON (
|
|
md.BranchCode = @branchCode AND
|
|
md.ItemNumber = @itemNumber AND
|
|
md.SequenceNumber = CAST(CAST(a.SequenceNumber AS INT) AS VARCHAR(32)) AND
|
|
@steptimestamp BETWEEN COALESCE(md.ReleaseDate, '1970-01-01') AND COALESCE(md.ObsoleteDate, '2029-01-01')
|
|
)
|
|
WHERE md.Status = 'Current';
|
|
|
|
--Exit if any MIS info found
|
|
IF (EXISTS ( SELECT *FROM @MIS ))
|
|
BEGIN
|
|
RETURN;
|
|
END
|
|
|
|
--Try to match to 'BackLevel'
|
|
INSERT INTO @MIS
|
|
(
|
|
WorkOrderNumber,
|
|
ItemNumber,
|
|
ItemDescription,
|
|
BranchCode,
|
|
WorkCenterCode,
|
|
StepTimestamp,
|
|
SequenceNumber,
|
|
FunctionCode,
|
|
FunctionOperationDescription,
|
|
MatchedSequenceNumber,
|
|
RoutingMatch,
|
|
MasterMatch,
|
|
MisNumber,
|
|
RevID,
|
|
CharNumber,
|
|
MisSequenceNumber,
|
|
TestDescription,
|
|
SamplingType,
|
|
SamplingValue,
|
|
ToolsGauges,
|
|
WorkInstructions,
|
|
Status,
|
|
ReleaseDate
|
|
)
|
|
SELECT @workOrderNumber,
|
|
@itemNumber,
|
|
@itemDescription,
|
|
@branchCode,
|
|
@workCenterCode,
|
|
@steptimestamp,
|
|
@sequenceNumber,
|
|
@functionCode,
|
|
@functionOperationDescription,
|
|
a.SequenceNumber AS MatchedSequenceNumber,
|
|
a.RoutingMatch,
|
|
a.MasterMatch,
|
|
md.MisNumber,
|
|
md.RevID,
|
|
md.CharNumber,
|
|
md.SequenceNumber AS MisSequenceNumber,
|
|
md.TestDescription,
|
|
md.SamplingType,
|
|
md.SamplingValue,
|
|
md.ToolsGauges,
|
|
md.WorkInstructions,
|
|
md.Status,
|
|
md.ReleaseDate
|
|
FROM @alias AS a INNER JOIN
|
|
dbo.MisData AS md ON (
|
|
md.BranchCode = @branchCode AND
|
|
md.ItemNumber = @itemNumber AND
|
|
md.SequenceNumber = CAST(CAST(a.SequenceNumber AS INT) AS VARCHAR(32)) AND
|
|
md.ReleaseDate BETWEEN @issueDate AND @steptimestamp
|
|
)
|
|
WHERE md.Status = 'BackLevel';
|
|
|
|
--Exit if any MIS info found
|
|
IF (EXISTS ( SELECT * FROM @MIS ))
|
|
BEGIN
|
|
RETURN;
|
|
END
|
|
|
|
INSERT INTO @MIS
|
|
(
|
|
WorkOrderNumber,
|
|
ItemNumber,
|
|
ItemDescription,
|
|
BranchCode,
|
|
WorkCenterCode,
|
|
StepTimestamp,
|
|
SequenceNumber,
|
|
FunctionCode,
|
|
FunctionOperationDescription,
|
|
MatchedSequenceNumber,
|
|
RoutingMatch,
|
|
MasterMatch
|
|
)
|
|
SELECT @workOrderNumber,
|
|
@itemNumber,
|
|
@itemDescription,
|
|
@branchCode,
|
|
@workCenterCode,
|
|
@steptimestamp,
|
|
@sequenceNumber,
|
|
@functionCode,
|
|
@functionOperationDescription,
|
|
a.SequenceNumber,
|
|
a.RoutingMatch,
|
|
a.MasterMatch
|
|
FROM @alias AS a;
|
|
|
|
--Exit if any MIS info found
|
|
IF (EXISTS ( SELECT * FROM @MIS ))
|
|
BEGIN
|
|
RETURN;
|
|
END
|
|
|
|
--Insert parameter data if no MIS or alias found
|
|
INSERT INTO @MIS
|
|
(
|
|
WorkOrderNumber,
|
|
ItemNumber,
|
|
ItemDescription,
|
|
BranchCode,
|
|
WorkCenterCode,
|
|
StepTimestamp,
|
|
SequenceNumber,
|
|
FunctionCode,
|
|
FunctionOperationDescription,
|
|
MatchedSequenceNumber,
|
|
RoutingMatch,
|
|
MasterMatch
|
|
)
|
|
VALUES
|
|
(
|
|
@workOrderNumber,
|
|
@itemNumber,
|
|
@itemDescription,
|
|
@branchCode,
|
|
@workCenterCode,
|
|
@steptimestamp,
|
|
@sequenceNumber,
|
|
@functionCode,
|
|
@functionOperationDescription,
|
|
NULL,
|
|
0,
|
|
0
|
|
);
|
|
|
|
RETURN;
|
|
END;
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.LastDataUpdates') IS NOT NULL
|
|
BEGIN
|
|
DROP VIEW [dbo].[LastDataUpdates];
|
|
END
|
|
|
|
GO
|
|
CREATE VIEW [dbo].[LastDataUpdates]
|
|
AS
|
|
WITH UPDATE_CTE
|
|
AS (
|
|
SELECT du.TableName,
|
|
du.UpdateType,
|
|
du.StartDT,
|
|
ROW_NUMBER() OVER (PARTITION BY du.TableName, du.UpdateType ORDER BY du.StartDT DESC) AS RN
|
|
FROM dbo.DataUpdate du
|
|
WHERE du.WasSuccessful = 1)
|
|
SELECT TableName,
|
|
COALESCE([3], '1970-01-01') AS MassUpdateDT,
|
|
COALESCE(COALESCE([2], [3]), '1970-01-01') AS DailyUpdateDT,
|
|
COALESCE(COALESCE(COALESCE([1], [2]), [3]), '1970-01-01') AS HourlyUpdateDT
|
|
FROM
|
|
(
|
|
SELECT UPDATE_CTE.TableName,
|
|
UPDATE_CTE.UpdateType,
|
|
UPDATE_CTE.StartDT,
|
|
UPDATE_CTE.RN
|
|
FROM UPDATE_CTE
|
|
WHERE RN = 1
|
|
) AS Source
|
|
PIVOT
|
|
(
|
|
MAX(StartDT)
|
|
FOR UpdateType IN ([1], [2], [3])
|
|
) AS PivotTable
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[WorkOrderTotalScrap]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.WorkOrderTotalScrap') IS NOT NULL
|
|
BEGIN
|
|
DROP VIEW [dbo].[WorkOrderTotalScrap];
|
|
END
|
|
|
|
GO
|
|
CREATE VIEW WorkOrderTotalScrap AS
|
|
SELECT woscrap.WorkOrderNumber,
|
|
COALESCE(SUM(woscrap.ScrappedQuantity), 0) AS TotalScrappedQuantity
|
|
FROM dbo.WorkOrderScrap AS woscrap
|
|
GROUP BY woscrap.WorkOrderNumber
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[CompleteSearch]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.CompleteSearch') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[CompleteSearch];
|
|
END
|
|
|
|
GO
|
|
CREATE PROCEDURE [dbo].[CompleteSearch] (
|
|
@p_SearchID INT,
|
|
@p_WasSuccessful BIT,
|
|
@p_Results VARBINARY(MAX)
|
|
)
|
|
AS
|
|
DECLARE @v_Status INT;
|
|
|
|
BEGIN
|
|
--Determine status code
|
|
SET @v_Status = CASE @p_WasSuccessful
|
|
WHEN 1 THEN 3
|
|
ELSE 4
|
|
END;
|
|
|
|
--Update search status and results
|
|
UPDATE dbo.Search
|
|
SET Status = @v_Status,
|
|
Results = @p_Results,
|
|
EndDT = GETDATE()
|
|
WHERE ID = @p_SearchID;
|
|
END
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[ResetPartialSearches]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.ResetPartialSearches') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[ResetPartialSearches];
|
|
END
|
|
|
|
GO
|
|
CREATE PROCEDURE [dbo].[ResetPartialSearches]
|
|
AS
|
|
BEGIN
|
|
--Reset status and start timestamp for searches begun but not finished
|
|
UPDATE dbo.Search
|
|
SET Status = 1,
|
|
StartDT = NULL
|
|
WHERE StartDT IS NOT NULL AND
|
|
EndDT IS NULL;
|
|
END
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[StartSearch]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.StartSearch') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[StartSearch];
|
|
END
|
|
|
|
GO
|
|
CREATE PROCEDURE [dbo].[StartSearch] (@p_SearchID INT)
|
|
AS
|
|
BEGIN
|
|
--Update search status and start timestamp
|
|
UPDATE dbo.Search
|
|
SET Status = 2,
|
|
StartDT = GETDATE()
|
|
WHERE ID = @p_SearchID;
|
|
END
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[SubmitSearch]...';
|
|
|
|
GO
|
|
IF OBJECT_ID('dbo.SubmitSearch') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[SubmitSearch];
|
|
END
|
|
|
|
GO
|
|
CREATE PROCEDURE [dbo].[SubmitSearch] (
|
|
@p_UserName VARCHAR(128),
|
|
@p_Name VARCHAR(128),
|
|
@p_Criteria VARCHAR(MAX),
|
|
@o_SearchID INT OUTPUT
|
|
)
|
|
AS
|
|
BEGIN
|
|
--Insert new search record
|
|
INSERT INTO Search
|
|
(
|
|
UserName,
|
|
Name,
|
|
Status,
|
|
SubmitDT,
|
|
Criteria
|
|
)
|
|
VALUES (
|
|
@p_UserName,
|
|
@p_Name,
|
|
1,
|
|
GETDATE(),
|
|
@p_Criteria
|
|
);
|
|
|
|
--Get assigned auto-ID
|
|
SET @o_SearchID = SCOPE_IDENTITY();
|
|
END
|
|
|
|
GO
|
|
PRINT N'Update complete.'; |