Samuele E. Locatelli
76 lines
4.6 KiB
Transact-SQL
76 lines
4.6 KiB
Transact-SQL
CREATE TABLE [dbo].[OrdersHist] (
|
|
[OrdNum] NVARCHAR (50) CONSTRAINT [DF_OrdersHist_OrdNum] DEFAULT ((0)) NOT NULL,
|
|
[OrdRow] NVARCHAR (50) CONSTRAINT [DF_OrdersHist_OrdRow] DEFAULT ((0)) NOT NULL,
|
|
[OrdDate] SMALLDATETIME CONSTRAINT [DF_OrdersHist_OrdDate] DEFAULT (getdate()) NOT NULL,
|
|
[CodClient] NVARCHAR (50) NOT NULL,
|
|
[CodItem] NVARCHAR (50) CONSTRAINT [DF_OrdersHist_CodItem] DEFAULT ('') NOT NULL,
|
|
[RawMat] NVARCHAR (50) NOT NULL,
|
|
[RawMatCost] DECIMAL (18, 6) CONSTRAINT [DF_OrdersHist_RawMatCost] DEFAULT ((0)) NOT NULL,
|
|
[BatchQty] INT CONSTRAINT [DF_OrdersHist_BatchQty] DEFAULT ((0)) NOT NULL,
|
|
[CodInco] NVARCHAR (5) NOT NULL,
|
|
[OrdPrice] DECIMAL (9, 6) CONSTRAINT [DF_OrdersHist_OrdPrice] DEFAULT ((0)) NOT NULL,
|
|
[RawMatExtraCost] DECIMAL (18, 6) NOT NULL,
|
|
[PriceOff] DECIMAL (9, 6) NOT NULL,
|
|
[OrdQty] INT NOT NULL,
|
|
[QuoteType] CHAR (1) CONSTRAINT [DF_OrdersHist_QuoteType] DEFAULT ('R') NOT NULL,
|
|
[CodQuote] BIGINT CONSTRAINT [DF_OrdersHist_CodQuote] DEFAULT ((0)) NOT NULL,
|
|
[QuoteRev] INT CONSTRAINT [DF_OrdersHist_QuoteRev] DEFAULT ((0)) NOT NULL,
|
|
[ProvvCost] DECIMAL (9, 6) CONSTRAINT [DF_OrdersHist_ProvvCost] DEFAULT ((0)) NOT NULL,
|
|
[EndCalc] DATETIME NULL,
|
|
CONSTRAINT [PK_OrdersHist] PRIMARY KEY CLUSTERED ([OrdNum] ASC, [OrdRow] ASC) WITH (FILLFACTOR = 100)
|
|
);
|
|
|
|
|
|
GO
|
|
CREATE NONCLUSTERED INDEX [idx_OH_quote]
|
|
ON [dbo].[OrdersHist]([CodQuote] ASC, [QuoteRev] ASC) WITH (FILLFACTOR = 100);
|
|
|
|
|
|
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'OrdersHist', @level2type = N'COLUMN', @level2name = N'CodQuote';
|
|
|
|
|
|
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'OrdersHist', @level2type = N'COLUMN', @level2name = N'QuoteType';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Prezzo finale ordine', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'OrdPrice';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Codice Incoterms', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'CodInco';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Quantità lotto kg', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'BatchQty';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'costo della MP €/kg', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'RawMatCost';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice MP', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'RawMat';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice articolo', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'CodItem';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'codice cliente', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'CodClient';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'data dell''ordine', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'OrdDate';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'riga ordine', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'OrdRow';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'numero ordine', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdersHist', @level2type = N'COLUMN', @level2name = N'OrdNum';
|
|
|