Samuele E. Locatelli
143 lines
8.8 KiB
Transact-SQL
143 lines
8.8 KiB
Transact-SQL
CREATE TABLE [dbo].[ProductionLog] (
|
|
[EventStart] DATETIME NOT NULL,
|
|
[EventEnd] DATETIME NOT NULL,
|
|
[CodPlant] NVARCHAR (50) NOT NULL,
|
|
[ProcessNum] NVARCHAR (50) NOT NULL,
|
|
[NrPos] NVARCHAR (50) CONSTRAINT [DF_ProductionLog_NrPos] DEFAULT ('') NOT NULL,
|
|
[CodDies] NVARCHAR (50) NOT NULL,
|
|
[CodClient] NVARCHAR (50) NOT NULL,
|
|
[OrderNum] NVARCHAR (50) NOT NULL,
|
|
[BatchNum] NVARCHAR (50) NOT NULL,
|
|
[ProcessType] NVARCHAR (50) NOT NULL,
|
|
[EventType] NVARCHAR (50) NOT NULL,
|
|
[RawMat] NVARCHAR (50) NOT NULL,
|
|
[CodPackag] NVARCHAR (50) NOT NULL,
|
|
[QtyIN] DECIMAL (18, 4) NOT NULL,
|
|
[QtyOUT] DECIMAL (18, 4) NOT NULL,
|
|
[QtyEXT] DECIMAL (18, 4) NOT NULL,
|
|
[NumIN] INT NOT NULL,
|
|
[NumOUT] INT NOT NULL,
|
|
[QuotaPlant] DECIMAL (9, 4) NOT NULL,
|
|
[QuotaMan] DECIMAL (9, 4) NOT NULL,
|
|
[Tags] NVARCHAR (250) NOT NULL,
|
|
[Duration] AS (CONVERT([decimal](12,6),datediff(second,[EventStart],[EventEnd])/(3600.00),(0))) PERSISTED,
|
|
[okPr] BIT CONSTRAINT [DF_ProductionLog_okPr] DEFAULT ((1)) NOT NULL,
|
|
[okQM] BIT CONSTRAINT [DF_ProductionLog_okQM] DEFAULT ((1)) NOT NULL,
|
|
[okSc] BIT CONSTRAINT [DF_ProductionLog_okSc] DEFAULT ((1)) NOT NULL,
|
|
[okDi] BIT CONSTRAINT [DF_ProductionLog_okDi] DEFAULT ((1)) NOT NULL,
|
|
CONSTRAINT [PK_ProductionLog_1] PRIMARY KEY CLUSTERED ([EventStart] ASC, [EventEnd] ASC, [CodPlant] ASC, [ProcessNum] ASC, [NrPos] ASC, [CodDies] ASC),
|
|
CONSTRAINT [FK_ProductionLog_ClientDet] FOREIGN KEY ([CodClient]) REFERENCES [dbo].[ClientDet] ([CodClient]) ON UPDATE CASCADE,
|
|
CONSTRAINT [FK_ProductionLog_PackagDet] FOREIGN KEY ([CodPackag]) REFERENCES [dbo].[PackagDet] ([CodPackag]) ON UPDATE CASCADE,
|
|
CONSTRAINT [FK_ProductionLog_PlantsDet] FOREIGN KEY ([CodPlant]) REFERENCES [dbo].[PlantsDet] ([CodPlant]) ON UPDATE CASCADE,
|
|
CONSTRAINT [FK_ProductionLog_RawMatDet] FOREIGN KEY ([RawMat]) REFERENCES [dbo].[RawMatDet] ([RawMat]) ON UPDATE CASCADE
|
|
);
|
|
|
|
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'inizio evento - formato INT 16 cifre yyyyMMddHHmmssnn dove nn = centesimi di sec oppure 00', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'EventStart';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'fine evento - formato INT 16 cifre yyyyMMddHHmmssnn dove nn = centesimi di sec oppure 00', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'EventEnd';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice impianto', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'CodPlant';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice fase, da qui si crea anagrafica fasi distinct', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'ProcessNum';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice matrice', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'CodDies';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice cliente', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'CodClient';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'rif codice ordine', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'OrderNum';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice lotto', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'BatchNum';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice del TIPO di fase, x raggruppamento funzionale', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'ProcessType';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'tipo evento: produzione, prova, guasto, setup...', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'EventType';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice MP', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'RawMat';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice imballo', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'CodPackag';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'kg materiale in ingresso', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'QtyIN';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'kg materiale BUONO prodotto in OUTput', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'QtyOUT';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'kg materiale dbora che fa parte dell''OUT, es imballi/tara', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'QtyEXT';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'qta materiale in ingresso', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'NumIN';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'qta materiale BUONO prodotto in OUTput', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'NumOUT';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'indicazione della quota macchina per produrre quanto registrato', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'QuotaPlant';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'indicazione della quota uomo per produrre quanto registrato', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'QuotaMan';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'categorie LIBERE, separatore #', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'Tags';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Durata espressa in ore', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'Duration';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'validazione riga x calcolo produttività', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'okPr';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'validazione riga x calcolo quota MAN', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'okQM';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'validazione riga x calcolo scarti', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'okSc';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'validazione riga x calcolo difettosità', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ProductionLog', @level2type = N'COLUMN', @level2name = N'okDi';
|
|
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [i_ProductionLog_CodDies]
|
|
ON [dbo].[ProductionLog]([CodDies] ASC)
|
|
INCLUDE([EventStart], [RawMat]);
|
|
|