Samuele E. Locatelli
150 lines
9.2 KiB
Transact-SQL
150 lines
9.2 KiB
Transact-SQL
CREATE TABLE [dbo].[QuoteWorkInt] (
|
|
[QuoteType] CHAR (1) CONSTRAINT [DF_QuoteWorkInt_QuoteType] DEFAULT ('Q') NOT NULL,
|
|
[CodQuote] BIGINT NOT NULL,
|
|
[QuoteRev] INT CONSTRAINT [DF_QuoteWorkInt_QuoteRev] DEFAULT ((0)) NOT NULL,
|
|
[NumWI] INT NOT NULL,
|
|
[ProcessNum] NVARCHAR (50) CONSTRAINT [DF_QuoteWorkInt_WorkType] DEFAULT ((0)) NOT NULL,
|
|
[CodPlant] NVARCHAR (50) NOT NULL,
|
|
[Class01] NVARCHAR (50) CONSTRAINT [DF_QuoteWorkInt_Prod_LUK] DEFAULT ('') NOT NULL,
|
|
[Class02] NVARCHAR (50) CONSTRAINT [DF_QuoteWorkInt_Class011] DEFAULT ('') NOT NULL,
|
|
[Class03] NVARCHAR (50) CONSTRAINT [DF_QuoteWorkInt_Class021] DEFAULT ('') NOT NULL,
|
|
[Class04] NVARCHAR (50) CONSTRAINT [DF_QuoteWorkInt_Class031] DEFAULT ('') NOT NULL,
|
|
[Class05] NVARCHAR (50) CONSTRAINT [DF_QuoteWorkInt_Class032] DEFAULT ('') NOT NULL,
|
|
[NetProd] DECIMAL (9, 3) CONSTRAINT [DF_QuoteWorkInt_NetProd] DEFAULT ((0)) NOT NULL,
|
|
[WSR] DECIMAL (9, 8) CONSTRAINT [DF_QuoteWorkInt_TSR] DEFAULT ((0)) NOT NULL,
|
|
[MSR] DECIMAL (9, 8) CONSTRAINT [DF_QuoteWorkInt_CSR] DEFAULT ((0)) NOT NULL,
|
|
[DSR] DECIMAL (9, 8) CONSTRAINT [DF_QuoteWorkInt_CSR1] DEFAULT ((0)) NOT NULL,
|
|
[FC4UG] DECIMAL (9, 6) CONSTRAINT [DF_QuoteWorkInt_RUFG] DEFAULT ((0)) NOT NULL,
|
|
[VC4UG] DECIMAL (9, 6) CONSTRAINT [DF_QuoteWorkInt_RCFG] DEFAULT ((0)) NOT NULL,
|
|
[OH4UG] DECIMAL (9, 6) CONSTRAINT [DF_QuoteWorkInt_VC4UG1] DEFAULT ((0)) NOT NULL,
|
|
[SC4UG] DECIMAL (9, 6) CONSTRAINT [DF_QuoteWorkInt_VC4UG1_1] DEFAULT ((0)) NOT NULL,
|
|
[WYR] AS ((1)-[WSR]),
|
|
[MYR] AS ((1)-[MSR]),
|
|
[DYR] AS ((1)-[DSR]),
|
|
[WCR] AS (case when ((1)-[WSR])=(0) then (1) else (1)/((1)-[WSR]) end),
|
|
[MCR] AS (case when ((1)-[MSR])=(0) then (1) else (1)/((1)-[MSR]) end),
|
|
[DCR] AS (case when ((1)-[DSR])=(0) then (1) else (1)/((1)-[DSR]) end),
|
|
[valid] BIT CONSTRAINT [DF_QuoteWorkInt_valid] DEFAULT ((1)) NOT NULL,
|
|
CONSTRAINT [PK_QuoteWorkInt] PRIMARY KEY CLUSTERED ([QuoteType] ASC, [CodQuote] ASC, [QuoteRev] ASC, [NumWI] ASC) WITH (FILLFACTOR = 90),
|
|
CONSTRAINT [FK_QuoteWorkInt_QuoteList] FOREIGN KEY ([QuoteType], [CodQuote], [QuoteRev]) REFERENCES [dbo].[QuoteList] ([QuoteType], [CodQuote], [QuoteRev]) ON UPDATE CASCADE
|
|
);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'tipo di preventivo: Q = quote, S = simulation', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'QuoteType';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'numero nel formato yyMMddnnnn dove nnn è incrementale giornaliero', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'CodQuote';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'progressivo interno', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'NumWI';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Tipo risorsa (e lavorazione): estrusione/imballo', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'ProcessNum';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice impianto', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'CodPlant';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Riclass. 01, ARTICOLO + MATERIALE', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'Class01';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Riclass. 02, codice di ricerca per prod - es cod imballo o Chiave di ricerca Articolo/ClasseArt + RawMat+ Impianto + NumLuci', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'Class02';
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Riclass. 03, tipo imballo', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'Class03';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Riclass. 04', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'Class04';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Riclass. 05', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'Class05';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Produttivita netta (teorica) kg/h', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'NetProd';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Work Scrape Ratio, percentuale scarto della lavorazione (tecnico)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'WSR';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Material Scrape Ratio, percentuale scarto legato alla MP (cesoia x alx)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'MSR';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Defect Scrape Ratio, percentuale scarto per difettosità prodotto (qualità)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'DSR';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Fixed Cost FOR Unit of Good, quota dei costi fissi per unità di prodotto finito (Copertura Costo Struttura e Linea)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'FC4UG';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Variables Cost FOR Unit of Good, quota dei costi variabili per unità di prodotto finito (Costo del personale x Alx)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'VC4UG';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'OH Cost FOR Unit of Good, quota dei costi Over Head per unità di prodotto finito', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'OH4UG';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Setup Cost FOR Unit of Good, quota dei costi di setup per unità di prodotto finito (costo cambio matrice x Alx)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'SC4UG';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Work Yield Ratio, percentuale RESA della lavorazione (tecnico), 1 - scarto', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'WYR';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Material Yield Ratio, percentuale RESA legato alla MP (cesoia x alx), 1 - scarto', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'MYR';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Defect Yield Ratio, percentuale RESA per difettosità prodotto (qualità), 1 - scarto', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'DYR';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Work Conversion Ratio, Messa a Cento, 1 / RESA', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'WCR';
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Material Conversion Ratio, Messa a Cento, 1 / RESA', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'MCR';
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Defect Conversion Ratio, Messa a Cento, 1 / RESA', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'QuoteWorkInt', @level2type = N'COLUMN', @level2name = N'DCR';
|
|
|
|
|
|
|
|
|
|
|