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
Generic Integration Module- DocsNext