Open navigation

Generic Integration Module- MSSQL Database Creation Scripts

USE [master]
GO

-- Create [RepSpark_API] in the default location
CREATE DATABASE [RepSpark_API]
GO

USE [RepSpark_Api]
GO

CREATE TABLE [Customers] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [CustomerCode] nvarchar(15) NULL,
    [StoreCode] nvarchar(50) NULL,
    [DBA] nvarchar(80) NULL,
    [Address1] nvarchar(150) NULL,
    [Address2] nvarchar(150) NULL,
    [City] nvarchar(50) NULL,
    [State] nvarchar(35) NULL,
    [Zip] nvarchar(15) NULL,
    [Country] nvarchar(25) NULL,
    [Name] nvarchar(80) NULL,
    [PhoneNumber] nvarchar(25) NULL,
    [FaxNumber] nvarchar(25) NULL,
    [SalesPersonCode] nvarchar(15) NULL,
    [ShippingMethodCode] nvarchar(15) NULL,
    [DiscountPercentage] decimal(18,2) NULL,
    [TermsCode] nvarchar(15) NULL,
    [TypeCode] nvarchar(15) NULL,
    [PricePlanCode] nvarchar(15) NULL,
    [Enabled] bit NULL,
    [IsBillTo] bit NULL,
    CONSTRAINT [PK_Customers] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Inventory] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [ProductNumber] nvarchar(20) NULL,
    [ColorCode] nvarchar(15) NULL,
    [GenderCode] nvarchar(15) NULL,
    [ProductCategoryCode] nvarchar(15) NULL,
    [SizeCode] nvarchar(15) NULL,
    [AvailableQuantity] int NOT NULL,
    [AvailableDate] datetime2 NOT NULL,
    [DimensionCode] nvarchar(15) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [InfiniteAvailability] bit NULL,
    [LocationCode] nvarchar(15) NULL,
    CONSTRAINT [PK_Inventory] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [InventoryLocations] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [LocationCode] nvarchar(15) NULL,
    [LocationDescription] nvarchar(255) NULL,
    [Active] bit NULL,
    CONSTRAINT [PK_InventoryLocations] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [InvoiceReports] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [InvoiceNumber] nvarchar(15) NULL,
    [OrderNumber] nvarchar(15) NULL,
    [PurchaseOrder] nvarchar(100) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [SeasonDescription] nvarchar(256) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [DivisionDescription] nvarchar(256) NULL,
    [SalesPersonCode] nvarchar(15) NULL,
    [SalesPersonDescription] nvarchar(256) NULL,
    [TermsCode] nvarchar(15) NULL,
    [TermsDescription] nvarchar(256) NULL,
    [ShippingMethodCode] nvarchar(15) NOT NULL,
    [ShippingMethodDescription] nvarchar(max) NULL,
    [BillingCustomerCode] nvarchar(15) NULL,
    [BillingCustomerName] nvarchar(80) NULL,
    [BillingDBA] nvarchar(80) NULL,
    [BillingAttn] nvarchar(80) NULL,
    [BillingAddress1] nvarchar(150) NULL,
    [BillingAddress2] nvarchar(150) NULL,
    [BillingCity] nvarchar(50) NULL,
    [BillingState] nvarchar(35) NULL,
    [BillingZip] nvarchar(15) NULL,
    [BillingCountry] nvarchar(30) NULL,
    [BillingTelephone] nvarchar(25) NULL,
    [BillingFaxNumber] nvarchar(25) NULL,
    [ShippingCustomerCode] nvarchar(15) NULL,
    [StoreNumber] nvarchar(50) NULL,
    [ShippingCustomerName] nvarchar(80) NULL,
    [ShippingDBA] nvarchar(80) NULL,
    [ShippingAttn] nvarchar(80) NULL,
    [ShippingAddress1] nvarchar(150) NULL,
    [ShippingAddress2] nvarchar(150) NULL,
    [ShippingCity] nvarchar(50) NULL,
    [ShippingState] nvarchar(35) NULL,
    [ShippingZip] nvarchar(15) NULL,
    [ShippingCountry] nvarchar(30) NULL,
    [ShippingTelephone] nvarchar(25) NULL,
    [ShippingFaxNumber] nvarchar(25) NULL,
    [InvoiceCreatedDate] datetime2 NOT NULL,
    [InvoiceSentDate] datetime2 NOT NULL,
    [FreightAmount] decimal(18,2) NOT NULL,
    [TrackingNumber] nvarchar(40) NULL,
    [GSTAmount] decimal(18,2) NOT NULL,
    CONSTRAINT [PK_InvoiceReports] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Options] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [Description] nvarchar(255) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [Hidden] bit NULL,
    [KeyCode] nvarchar(15) NULL,
    [OrderBy] int NULL,
    [ParentCode] nvarchar(15) NULL,
    [StringValue1] nvarchar(255) NULL,
    [ElementType] nvarchar(50) NULL,
    CONSTRAINT [PK_Options] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [OrderConfirmations] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [RepSparkOrderNumber] int NOT NULL,
    [ErpOrderNumber] nvarchar(50) NULL,
    CONSTRAINT [PK_OrderConfirmations] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [OrderReports] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [OrderNumber] nvarchar(15) NULL,
    [RSKOrderId] int NULL,
    [BillingCustomerCode] nvarchar(15) NULL,
    [BillingCustomerName] nvarchar(80) NULL,
    [BillingDBA] nvarchar(80) NULL,
    [BillingAttn] nvarchar(80) NULL,
    [BillingAddress1] nvarchar(150) NULL,
    [BillingAddress2] nvarchar(150) NULL,
    [BillingCity] nvarchar(50) NULL,
    [BillingState] nvarchar(35) NULL,
    [BillingZip] nvarchar(15) NULL,
    [BillingCountry] nvarchar(30) NULL,
    [BillingTelephone] nvarchar(25) NULL,
    [BillingFaxNumber] nvarchar(25) NULL,
    [ShippingCustomerCode] nvarchar(15) NULL,
    [StoreNumber] nvarchar(50) NULL,
    [ShippingCustomerName] nvarchar(80) NULL,
    [ShippingDBA] nvarchar(80) NULL,
    [ShippingAttn] nvarchar(80) NULL,
    [ShippingAddress1] nvarchar(150) NULL,
    [ShippingAddress2] nvarchar(150) NULL,
    [ShippingCity] nvarchar(50) NULL,
    [ShippingState] nvarchar(35) NULL,
    [ShippingZip] nvarchar(15) NULL,
    [ShippingCountry] nvarchar(30) NULL,
    [ShippingTelephone] nvarchar(25) NULL,
    [ShippingFaxNumber] nvarchar(25) NULL,
    [PurchaseOrder] nvarchar(50) NULL,
    [EntryDate] datetime2 NULL,
    [OrderDate] datetime2 NULL,
    [StartDate] datetime2 NULL,
    [CancelDate] datetime2 NULL,
    [CustomerTypeCode] nvarchar(15) NULL,
    [CustomerTypeDescription] nvarchar(256) NULL,
    [PricingPlanCode] nvarchar(15) NULL,
    [TypeCode] nvarchar(15) NULL,
    [TypeDescription] nvarchar(256) NULL,
    [TermsCode] nvarchar(15) NULL,
    [TermsDescription] nvarchar(256) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [SeasonDescription] nvarchar(256) NULL,
    [MarketingSeasonCode] nvarchar(15) NULL,
    [MarketingSeasonDescription] nvarchar(256) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [DivisionDescription] nvarchar(256) NULL,
    [SalesPersonCode] nvarchar(15) NULL,
    [SalesPersonDescription] nvarchar(256) NULL,
    [ShippingMethodCode] nvarchar(15) NULL,
    [ShippingMethodDescription] nvarchar(256) NULL,
    CONSTRAINT [PK_OrderReports] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Orders] (
    [OrderId] int NOT NULL,
    [UserName] nvarchar(100) NULL,
    [BrandCode] nvarchar(15) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [CustomerCode] nvarchar(15) NULL,
    [StoreCode] nvarchar(50) NULL,
    [SalesPersonCode] nvarchar(15) NULL,
    [PurchaseOrder] nvarchar(50) NULL,
    [EntryDate] datetime2 NOT NULL,
    [OrderDate] datetime2 NOT NULL,
    [StartDate] datetime2 NULL,
    [CancelDate] datetime2 NULL,
    [InHandDate] datetime2 NULL,
    [LastUpdated] datetime2 NULL,
    [LastUpdatedBy] nvarchar(100) NULL,
    [TypeCode] nvarchar(15) NULL,
    [Comments] nvarchar(max) NULL,
    [TermsCode] nvarchar(max) NULL,
    [ShipViaCode] nvarchar(15) NULL,
    [SpecialHandling] bit NOT NULL,
    [DropShipName] nvarchar(80) NULL,
    [DropShipAttn] nvarchar(80) NULL,
    [DropShipAddress1] nvarchar(150) NULL,
    [DropShipAddress2] nvarchar(150) NULL,
    [DropShipCity] nvarchar(50) NULL,
    [DropShipState] nvarchar(25) NULL,
    [DropShipZip] nvarchar(15) NULL,
    [DropShipCountry] nvarchar(25) NULL,
    [PriceModification] decimal(18,2) NOT NULL,
    [PricingTierCode] nvarchar(15) NULL,
    [CatalogCode] nvarchar(15) NULL,
    [StatusId] int NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY ([OrderId])
);

GO

CREATE TABLE [PricingMinimums] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [GroupCode] nvarchar(15) NULL,
    [TypeCode] nvarchar(15) NULL,
    [MinimumQuantity] int NOT NULL,
    [PricingTierCode] nvarchar(15) NULL,
    CONSTRAINT [PK_PricingMinimums] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Products] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(10) NULL,
    [ProductNumber] nvarchar(20) NULL,
    [ProductName] nvarchar(100) NULL,
    [LongDescription] nvarchar(max) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [ColorCode] nvarchar(15) NULL,
    [GenderCode] nvarchar(15) NULL,
    [CatalogCode] nvarchar(15) NULL,
    [CategoryCode] nvarchar(15) NULL,
    [RetailPrice] decimal(18,2) NULL,
    [DiscountedPrice] decimal(18,2) NULL,
    [SizeScaleCode] nvarchar(15) NULL,
    [ImageURL] nvarchar(256) NULL,
    [ProductType] int NULL,
    [WholesalePrice] decimal(18,2) NOT NULL,
    [DimensionCode] nvarchar(10) NULL,
    [Enabled] bit NULL,
    [AssociationsPerGroup] int NULL,
    [MarketingSeasonCode] nvarchar(10) NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Seasons] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [Description] nvarchar(255) NULL,
    [ParentSeasonCode] nvarchar(15) NULL,
    [StartOrderDate] datetime2 NULL,
    [EndOrderDate] datetime2 NULL,
    [StartShipDate] datetime2 NULL,
    [EndShipDate] datetime2 NULL,
    [Enabled] bit NULL,
    CONSTRAINT [PK_Seasons] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Sizings] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(10) NULL,
    [Enabled] bit NULL,
    [SizeCode] nvarchar(15) NULL,
    [SizeDescription] nvarchar(20) NULL,
    [SizeIndex] int NOT NULL,
    [SizeScaleCode] nvarchar(15) NULL,
    CONSTRAINT [PK_Sizings] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Upcs] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(10) NULL,
    [ProductNumber] nvarchar(20) NULL,
    [ColorCode] nvarchar(15) NULL,
    [GenderCode] nvarchar(15) NULL,
    [ProductCategoryCode] nvarchar(15) NULL,
    [SizeCode] nvarchar(15) NULL,
    [DimensionCode] nvarchar(15) NULL,
    [UPC] nvarchar(20) NULL,
    CONSTRAINT [PK_Upcs] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [WiwoSummaries] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [DivisionCode] nvarchar(15) NULL,
    [CustomerCode] nvarchar(15) NULL,
    [CustomerName] nvarchar(50) NULL,
    [CustomerType] nvarchar(30) NULL,
    [SalesPersonCode] nvarchar(15) NULL,
    [Region] nvarchar(2) NULL,
    [LYPreSeasonTD] decimal(18,2) NOT NULL,
    [LYTotalShip] decimal(18,2) NOT NULL,
    [LYPreSeasonAsOf] decimal(18,2) NOT NULL,
    [CurrYTDPreSeason] decimal(18,2) NOT NULL,
    [VarCurrPreToLYAsOf] decimal(18,2) NOT NULL,
    [VarCurrPreToLYShipTotal] decimal(18,2) NOT NULL,
    CONSTRAINT [PK_WiwoSummaries] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [CustomerExtensions] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [ElementType] nvarchar(50) NULL,
    [Value] nvarchar(max) NULL,
    [CustomerId] int NOT NULL,
    CONSTRAINT [PK_CustomerExtensions] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_CustomerExtensions_Customers_CustomerId] FOREIGN KEY ([CustomerId]) REFERENCES [Customers] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [InvoiceItems] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [InvoiceReportId] int NOT NULL,
    [BrandCode] nvarchar(10) NULL,
    [InvoiceLineNumber] nvarchar(10) NULL,
    [ProductNumber] nvarchar(20) NOT NULL,
    [ProductName] nvarchar(100) NULL,
    [OrderNumber] nvarchar(15) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [SeasonDescription] nvarchar(256) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [DivisionDescription] nvarchar(256) NULL,
    [ColorCode] nvarchar(15) NULL,
    [ColorDescription] nvarchar(256) NULL,
    [GenderCode] nvarchar(15) NULL,
    [GenderDescription] nvarchar(256) NULL,
    [DimensionCode] nvarchar(15) NULL,
    [SizeScaleCode] nvarchar(15) NULL,
    [ProductCategoryCode] nvarchar(15) NULL,
    [ProductCategoryDescription] nvarchar(256) NULL,
    CONSTRAINT [PK_InvoiceItems] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_InvoiceItems_InvoiceReports_InvoiceReportId] FOREIGN KEY ([InvoiceReportId]) REFERENCES [InvoiceReports] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderItemReports] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [OrderNumber] nvarchar(15) NULL,
    [LineNumber] nvarchar(10) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [SeasonDescription] nvarchar(256) NULL,
    [MarketingSeasonCode] nvarchar(15) NULL,
    [MarketingSeasonDescription] nvarchar(256) NULL,
    [DivisionCode] nvarchar(15) NULL,
    [DivisionDescription] nvarchar(256) NULL,
    [ProductNumber] nvarchar(20) NULL,
    [ProductName] nvarchar(100) NULL,
    [ColorCode] nvarchar(15) NULL,
    [ColorDescription] nvarchar(256) NULL,
    [ProductClassificationCode] nvarchar(15) NULL,
    [ProductClassificationDescription] nvarchar(15) NULL,
    [GenderCode] nvarchar(15) NULL,
    [GenderDescription] nvarchar(256) NULL,
    [DimensionCode] nvarchar(15) NULL,
    [DimensionDescription] nvarchar(256) NULL,
    [SizeScaleCode] nvarchar(15) NULL,
    [ProductCategoryCode] nvarchar(15) NULL,
    [ProductCategoryDescription] nvarchar(256) NULL,
    [OrderReportId] int NULL,
    CONSTRAINT [PK_OrderItemReports] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_OrderItemReports_OrderReports_OrderReportId] FOREIGN KEY ([OrderReportId]) REFERENCES [OrderReports] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderExtensions] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [ElementType] nvarchar(50) NULL,
    [Value] nvarchar(max) NULL,
    [OrderId] int NULL,
    CONSTRAINT [PK_OrderExtensions] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_OrderExtensions_Orders_OrderId] FOREIGN KEY ([OrderId]) REFERENCES [Orders] ([OrderId]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderItems] (
    [OrderItemId] int NOT NULL,
    [OrderId] int NULL,
    CONSTRAINT [PK_OrderItems] PRIMARY KEY ([OrderItemId]),
    CONSTRAINT [FK_OrderItems_Orders_OrderId] FOREIGN KEY ([OrderId]) REFERENCES [Orders] ([OrderId]) ON DELETE CASCADE
);

GO

CREATE TABLE [ProductExtensions] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [ElementType] nvarchar(50) NULL,
    [Value] nvarchar(max) NULL,
    [ProductId] int NULL,
    CONSTRAINT [PK_ProductExtensions] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_ProductExtensions_Products_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [Products] ([Id]) ON DELETE NO ACTION
);

GO

CREATE TABLE [ProductImages] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [ImageUrl] nvarchar(max) NULL,
    [ProductId] int NULL,
    CONSTRAINT [PK_ProductImages] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_ProductImages_Products_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [Products] ([Id]) ON DELETE NO ACTION
);

GO

CREATE TABLE [ProductPricings] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [PricePlanCode] nvarchar(10) NULL,
    [RetailPrice] decimal(18,2) NULL,
    [WholesalePrice] decimal(18,2) NOT NULL,
    [DiscountedPrice] decimal(18,2) NULL,
    [CurrencyCode] nvarchar(50) NULL,
    [ProductId] int NULL,
    CONSTRAINT [PK_ProductPricings] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_ProductPricings_Products_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [Products] ([Id]) ON DELETE NO ACTION
);

GO

CREATE TABLE [InvoiceItemSizes] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [InvoiceItemReportId] int NOT NULL,
    [SizeCode] nvarchar(15) NULL,
    [OrderNumber] nvarchar(15) NULL,
    [SizeIndex] int NULL,
    [UPC] nvarchar(20) NULL,
    [InvoicedQuantity] int NULL,
    [SizeInvoicedPrice] decimal(18,2) NULL,
    [StatusCode] nvarchar(10) NULL,
    CONSTRAINT [PK_InvoiceItemSizes] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_InvoiceItemSizes_InvoiceItems_InvoiceItemReportId] FOREIGN KEY ([InvoiceItemReportId]) REFERENCES [InvoiceItems] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderItemSizeReports] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [BrandCode] nvarchar(15) NULL,
    [OrderNumber] nvarchar(15) NULL,
    [LineNumber] nvarchar(10) NULL,
    [StatusCode] nvarchar(10) NULL,
    [PickTicketNumber] nvarchar(10) NULL,
    [CancelOrReturnedReasonCode] nvarchar(15) NULL,
    [CancelOrReturnedReasonDescription] nvarchar(256) NULL,
    [InvoiceNumber] nvarchar(15) NULL,
    [LastTransactionDate] datetime2 NULL,
    [SizeCode] nvarchar(15) NULL,
    [SizeIndex] int NULL,
    [UPC] nvarchar(20) NULL,
    [SizeSellPrice] decimal(18,2) NULL,
    [SizeWholesalePrice] decimal(18,2) NULL,
    [SizeRetailPrice] decimal(18,2) NULL,
    [SizeCost] decimal(18,2) NULL,
    [OrderedQuantity] int NULL,
    [OrderItemReportId] int NULL,
    CONSTRAINT [PK_OrderItemSizeReports] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_OrderItemSizeReports_OrderItemReports_OrderItemReportId] FOREIGN KEY ([OrderItemReportId]) REFERENCES [OrderItemReports] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderItemSets] (
    [OrderItemSetId] int NOT NULL,
    [OrderItemId] int NULL,
    CONSTRAINT [PK_OrderItemSets] PRIMARY KEY ([OrderItemSetId]),
    CONSTRAINT [FK_OrderItemSets_OrderItems_OrderItemId] FOREIGN KEY ([OrderItemId]) REFERENCES [OrderItems] ([OrderItemId]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderItemSetProducts] (
    [OrderItemSetProductId] int NOT NULL,
    [ProductType] int NOT NULL,
    [DivisionCode] nvarchar(15) NULL,
    [SeasonCode] nvarchar(15) NULL,
    [ProductNumber] nvarchar(20) NULL,
    [ColorCode] nvarchar(15) NULL,
    [GenderCode] nvarchar(15) NULL,
    [ProductName] nvarchar(100) NULL,
    [ProductCategoryCode] nvarchar(15) NULL,
    [DimensionCode] nvarchar(15) NULL,
    [SizeScaleCode] nvarchar(15) NULL,
    [LocationCode] nvarchar(15) NULL,
    [OrderedPrice] decimal(18,2) NOT NULL,
    [CurrencyCode] nvarchar(max) NULL,
    [GimApiOrderItemSetId] int NOT NULL,
    CONSTRAINT [PK_OrderItemSetProducts] PRIMARY KEY ([OrderItemSetProductId]),
    CONSTRAINT [FK_OrderItemSetProducts_OrderItemSets_GimApiOrderItemSetId] FOREIGN KEY ([GimApiOrderItemSetId]) REFERENCES [OrderItemSets] ([OrderItemSetId]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderItemSetSizes] (
    [OrderItemSetSizeId] int NOT NULL,
    [Quantity] int NOT NULL,
    [SizeCode] nvarchar(15) NULL,
    [SizeIndex] int NOT NULL,
    [UPC] nvarchar(20) NULL,
    [GimApiOrderItemSetId] int NOT NULL,
    CONSTRAINT [PK_OrderItemSetSizes] PRIMARY KEY ([OrderItemSetSizeId]),
    CONSTRAINT [FK_OrderItemSetSizes_OrderItemSets_GimApiOrderItemSetId] FOREIGN KEY ([GimApiOrderItemSetId]) REFERENCES [OrderItemSets] ([OrderItemSetId]) ON DELETE CASCADE
);

GO

CREATE TABLE [OrderItemSetProductExtensions] (
    [Id] int NOT NULL IDENTITY,
    [CreateDate] datetime2 NULL,
    [UpdateDate] datetime2 NULL,
    [ElementType] nvarchar(50) NULL,
    [Value] nvarchar(max) NULL,
    [OrderItemSetProductId] int NOT NULL,
    CONSTRAINT [PK_OrderItemSetProductExtensions] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_OISProductExtensions_OISProducts_OISProductId] FOREIGN KEY ([OrderItemSetProductId]) REFERENCES [OrderItemSetProducts] ([OrderItemSetProductId]) ON DELETE CASCADE
);

GO

CREATE INDEX [IX_CustomerExtensions_CustomerId] ON [CustomerExtensions] ([CustomerId]);

GO

CREATE INDEX [IX_InvoiceItems_InvoiceReportId] ON [InvoiceItems] ([InvoiceReportId]);

GO

CREATE INDEX [IX_InvoiceItemSizes_InvoiceItemReportId] ON [InvoiceItemSizes] ([InvoiceItemReportId]);

GO

CREATE INDEX [IX_OrderExtensions_OrderId] ON [OrderExtensions] ([OrderId]);

GO

CREATE INDEX [IX_OrderItemReports_OrderReportId] ON [OrderItemReports] ([OrderReportId]);

GO

CREATE INDEX [IX_OrderItems_OrderId] ON [OrderItems] ([OrderId]);

GO

CREATE INDEX [IX_OrderItemSetProductExtensions_OrderItemSetProductId] ON [OrderItemSetProductExtensions] ([OrderItemSetProductId]);

GO

CREATE INDEX [IX_OrderItemSetProducts_GimApiOrderItemSetId] ON [OrderItemSetProducts] ([GimApiOrderItemSetId]);

GO

CREATE INDEX [IX_OrderItemSets_OrderItemId] ON [OrderItemSets] ([OrderItemId]);

GO

CREATE INDEX [IX_OrderItemSetSizes_GimApiOrderItemSetId] ON [OrderItemSetSizes] ([GimApiOrderItemSetId]);

GO

CREATE INDEX [IX_OrderItemSizeReports_OrderItemReportId] ON [OrderItemSizeReports] ([OrderItemReportId]);

GO

CREATE INDEX [IX_ProductExtensions_ProductId] ON [ProductExtensions] ([ProductId]);

GO

CREATE INDEX [IX_ProductImages_ProductId] ON [ProductImages] ([ProductId]);

GO

CREATE INDEX [IX_ProductPricings_ProductId] ON [ProductPricings] ([ProductId]);

GO


Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.