Index: RedGateDatabaseInfo.xml =================================================================== diff -u --- RedGateDatabaseInfo.xml (revision 0) +++ RedGateDatabaseInfo.xml (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,64 @@ + + + UTF8 + SQL_Latin1_General_CP1_CI_AS + dbo + dbo + PRIMARY + 13 + False + Default + 10485760 + 3dc238e2-62fc-46a1-9aa4-af796d3b82f7 + + + + + Tables
+ Stored Procedures + Views + Defaults + Storage\Full Text Catalogs + Functions + Security\Roles + Rules + Security\Users + Types\User-defined Data Types + + + Database Triggers + Assemblies + Synonyms + Types\XML Schema Collections + Service Broker\Message Types + Service Broker\Contracts + Service Broker\Queues + Service Broker\Services + Service Broker\Routes + Service Broker\Event Notifications + Storage\Partition Schemes + Storage\Partition Functions + + + + + Security\Schemas + Service Broker\Remote Service Bindings + Security\Certificates + Security\Symmetric Keys + Security\Asymmetric Keys + + + Storage\Full Text Stoplists + Extended Properties + Data + Sequences + Search Property Lists + Security Policies + + True + + + 0 + + \ No newline at end of file Index: Security/Roles/db_datareader.sql =================================================================== diff -u --- Security/Roles/db_datareader.sql (revision 0) +++ Security/Roles/db_datareader.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,2 @@ +ALTER ROLE [db_datareader] ADD MEMBER [calcpro_user] +GO Index: Security/Roles/db_datawriter.sql =================================================================== diff -u --- Security/Roles/db_datawriter.sql (revision 0) +++ Security/Roles/db_datawriter.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,2 @@ +ALTER ROLE [db_datawriter] ADD MEMBER [calcpro_user] +GO Index: Security/Users/calcpro_user.sql =================================================================== diff -u --- Security/Users/calcpro_user.sql (revision 0) +++ Security/Users/calcpro_user.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,2 @@ +CREATE USER [calcpro_user] FOR LOGIN [calcpro_user] +GO Index: Stored Procedures/dbo.etlVendor.sql =================================================================== diff -u --- Stored Procedures/dbo.etlVendor.sql (revision 0) +++ Stored Procedures/dbo.etlVendor.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,53 @@ +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO +CREATE PROCEDURE [dbo].[etlVendor] +AS +BEGIN + + TRUNCATE TABLE dbo.Vendor + + INSERT INTO dbo.Vendor + SELECT [vendorId] + ,[vendorName] + ,[vendorAddress1] + ,[vendorAddress2] + ,[vendorCity] + ,[vendorState] + ,[vendorZip] + ,[vendorPhone] + ,[vendorContact] + ,vV.[buyerLocation] + ,[buyer] + ,[buyerConcat] + ,[pickId] + ,[pickName] + ,[vendorStatus] + ,[discontinuedDate] + ,[inactiveDate] + ,vB.buyerId + ,vB.buyerName + ,vB.buyerGroup + ,vB.createDate + ,vB.updateDate + ,'isBuyer' = CASE WHEN gx_id2 <> '' THEN 1 + ELSE 0 + END + ,'buyerManagerGlobalId' = GX.gx_id1 + ,'buyerGlobalId' = GX.gx_desc + FROM [LES7].[Disdb].[dbo].[vwVendor] vV + LEFT JOIN [LES7.PROD].[Disdb].[dbo].[vwBuyer] vB + ON vV.buyerLocation = vV.buyerLocation + AND vV.buyer = vB.buyerId + LEFT JOIN [LES7.PROD].[Disdb].[dbo].[gxref] GX + ON GX.gx_id2 = vV.buyerConcat + AND gx_type='PUXREF' AND gx_id2 <> '' + + UPDATE dbo.Vendor + SET buyerManagerGlobalId = GX.gx_desc + FROM dbo.Vendor V INNER JOIN [LES7.PROD].[Disdb].[dbo].[gxref] GX + ON GX.gx_id1 = V.buyerManagerGlobalId + AND V.buyerManagerGlobalId IS NOT NULL +END +GO Index: Synonyms/dbo.TypeXref.sql =================================================================== diff -u --- Synonyms/dbo.TypeXref.sql (revision 0) +++ Synonyms/dbo.TypeXref.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,2 @@ +CREATE SYNONYM [dbo].[TypeXref] FOR [Common].[dbo].[TypeXref] +GO Index: Tables/dbo.Catalog.sql =================================================================== diff -u --- Tables/dbo.Catalog.sql (revision 0) +++ Tables/dbo.Catalog.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,24 @@ +CREATE TABLE [dbo].[Catalog] +( +[catalogId] [int] NOT NULL IDENTITY(1, 1), +[year] [int] NULL, +[catalogName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[regionId] [int] NOT NULL CONSTRAINT [DF_Catalog_regionId] DEFAULT ((39100)), +[catalogCodeId] [int] NOT NULL CONSTRAINT [DF_Catalog_catalogCodeId] DEFAULT ((39027)), +[statusCodeId] [int] NOT NULL CONSTRAINT [DF_Catalog_statusCodeId] DEFAULT ((39300)), +[dateCreated] [datetime] NOT NULL CONSTRAINT [DF_Catalog_dateCreated] DEFAULT (getdate()), +[createdBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Catalog_createdBy] DEFAULT (suser_sname()), +[lastUpdated] [datetime] NOT NULL CONSTRAINT [DF_Catalog_lastUpdated] DEFAULT (getdate()), +[lastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Catalog_lastUpdatedBy] DEFAULT (suser_sname()), +[dateDeleted] [datetime] NULL, +[deletedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[version] [int] NOT NULL CONSTRAINT [DF_Catalog_version] DEFAULT ((0)), +[catalogTypeId] [int] NULL CONSTRAINT [DF_Catalog_catalogTypeId] DEFAULT ((39201)), +[mainframeCatalogCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[dueDate] [datetime] NULL +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[Catalog] ADD CONSTRAINT [PK_Catalog_catalogId] PRIMARY KEY CLUSTERED ([catalogId]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[Catalog] ADD CONSTRAINT [catalog_UNIQ] UNIQUE NONCLUSTERED ([catalogName], [dateDeleted]) ON [PRIMARY] +GO Index: Tables/dbo.CatalogSection.sql =================================================================== diff -u --- Tables/dbo.CatalogSection.sql (revision 0) +++ Tables/dbo.CatalogSection.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,28 @@ +CREATE TABLE [dbo].[CatalogSection] +( +[catalogId] [int] NOT NULL, +[sectionId] [int] NOT NULL, +[sectionName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[startPageNumber] [int] NOT NULL, +[sectionNumber] [int] NOT NULL, +[statusCodeId] [int] NOT NULL CONSTRAINT [DF_CatalogSection_statusCodeId] DEFAULT ((39300)), +[calcsStartDate] [datetime] NULL, +[calcsDueDate] [datetime] NULL, +[writingDueDate] [datetime] NULL, +[photoDueDate] [datetime] NULL, +[dueDate] [datetime] NULL, +[dateCreated] [datetime] NOT NULL CONSTRAINT [DF_CatalogSection_dateCreated] DEFAULT (getdate()), +[createdBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CatalogSection_createdBy] DEFAULT (suser_sname()), +[lastUpdated] [datetime] NOT NULL CONSTRAINT [DF_CatalogSection_lastUpdated] DEFAULT (getdate()), +[lastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CatalogSection_lastUpdatedBy] DEFAULT (suser_sname()), +[dateDeleted] [datetime] NULL, +[deletedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[version] [int] NOT NULL CONSTRAINT [DF_CatalogSection_version] DEFAULT ((0)) +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSection] ADD CONSTRAINT [PK_CatalogSection_CatalogId_SectionId] PRIMARY KEY CLUSTERED ([catalogId], [sectionId]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSection] ADD CONSTRAINT [catalogSection_UNIQ] UNIQUE NONCLUSTERED ([catalogId], [sectionName], [dateDeleted]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSection] ADD CONSTRAINT [FK_CatalogSection_Catalog] FOREIGN KEY ([catalogId]) REFERENCES [dbo].[Catalog] ([catalogId]) +GO Index: Tables/dbo.CatalogSectionVendor.sql =================================================================== diff -u --- Tables/dbo.CatalogSectionVendor.sql (revision 0) +++ Tables/dbo.CatalogSectionVendor.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,136 @@ +CREATE TABLE [dbo].[CatalogSectionVendor] +( +[catalogId] [int] NOT NULL, +[sectionId] [int] NOT NULL, +[vendorId] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[writerId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[photoCoOrdinatorId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[calReceivedDate] [datetime] NULL, +[calcsStartDate] [datetime] NULL, +[calcsDueDate] [datetime] NULL, +[revisedDate] [datetime] NULL, +[writingStartDate] [datetime] NULL, +[writingDoneDate] [datetime] NULL, +[pcProofing] [datetime] NULL, +[prooferId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[proofStartDate] [datetime] NULL, +[proofDoneDate] [datetime] NULL, +[pagCheckInDate] [datetime] NULL, +[statusCodeId] [int] NOT NULL CONSTRAINT [DF_CatalogSectionVendor_statusCodeId] DEFAULT ((39300)), +[writingDueDate] [datetime] NULL, +[photoDueDate] [datetime] NULL, +[sectionVendorStartDate] [datetime] NULL, +[sectionVendorDueDate] [datetime] NULL, +[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[dateCreated] [datetime] NOT NULL CONSTRAINT [DF_CatalogSectionVendor_dateCreated] DEFAULT (getdate()), +[createdBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CatalogSectionVendor_createdBy] DEFAULT (suser_sname()), +[lastUpdated] [datetime] NOT NULL CONSTRAINT [DF_CatalogSectionVendor_lastUpdated] DEFAULT (getdate()), +[lastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CatalogSectionVendor_lastUpdatedBy] DEFAULT (suser_sname()), +[dateDeleted] [datetime] NULL, +[deletedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[version] [int] NOT NULL CONSTRAINT [DF_CatalogSectionVendor_version] DEFAULT ((0)), +[vendorPageNumber] [int] NOT NULL +) ON [PRIMARY] +GO +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO +CREATE TRIGGER [dbo].[Update_CatalogSectionVendor_trigger] +ON [dbo].[CatalogSectionVendor] +AFTER UPDATE,INSERT +AS +BEGIN + INSERT INTO dbo.CatalogSectionVendorHistory( + [catalogId], + [sectionId], + [vendorId], + [vendorPageNumber], + [writerId], + [photoCoOrdinatorId], + [calReceivedDate], + [calcsStartDate], + [calcsDueDate], + [revisedDate], + [writingStartDate], + [writingDoneDate], + [pcProofing], + [prooferId], + [proofStartDate], + [proofDoneDate], + [pagCheckInDate], + [statusCodeId], + [writingDueDate], + [photoDueDate], + [sectionVendorStartDate], + [sectionVendorDueDate], + [dateCreated], + [createdBy], + [lastUpdated], + [lastUpdatedBy], + [dateDeleted], + [deletedBy], + [version] + ) + SELECT + d.[catalogId], + d.[sectionId], + d.[vendorId], + d.[vendorPageNumber], + d.[writerId], + d.[photoCoOrdinatorId], + d.[calReceivedDate], + d.[calcsStartDate], + d.[calcsDueDate], + d.[revisedDate], + d.[writingStartDate], + d.[writingDoneDate], + d.[pcProofing], + d.[prooferId], + d.[proofStartDate], + d.[proofDoneDate], + d.[pagCheckInDate], + d.[statusCodeId], + d.[writingDueDate], + d.[photoDueDate], + d.[sectionVendorStartDate], + d.[sectionVendorDueDate], + d.[dateCreated], + d.[createdBy], + Getdate(), + i.[lastUpdatedBy], + d.[dateDeleted], + d.[deletedBy], + d.[version] + FROM inserted i + LEFT JOIN deleted d + ON i.[catalogId] = d.[catalogId] + AND i.[sectionId] = d.[sectionId] + AND i.[vendorId] = d.[vendorId] + -- AND i.[lastUpdated] <> d.[lastUpdated] + WHERE ( i.[calReceivedDate] != d.[calReceivedDate] ) + OR ( i.[calcsStartDate] != d.[calcsStartDate] ) + OR ( i.[calcsDueDate] != d.[calcsDueDate]) + OR ( i.[revisedDate] != d.[revisedDate]) + OR ( i.[writingStartDate] != d.[writingStartDate]) + OR ( i.[writingDoneDate] != d.[writingDoneDate]) + OR ( i.[pcProofing] != d.[pcProofing]) + OR ( i.[prooferId] != d.[prooferId]) + OR ( i.[proofStartDate] != d.[proofStartDate]) + OR ( i.[proofDoneDate] != d.[proofDoneDate]) + OR ( i.[pagCheckInDate] != d.[pagCheckInDate]) + OR ( i.[statusCodeId] != d.[statusCodeId]) + OR ( i.[writingDueDate] != d.[writingDueDate]) + OR ( i.[photoDueDate] != d.[photoDueDate]) + OR ( i.[sectionVendorStartDate] != d.[sectionVendorStartDate]) + OR ( i.[sectionVendorDueDate] != d.[sectionVendorDueDate]) +END; +GO +ALTER TABLE [dbo].[CatalogSectionVendor] ADD CONSTRAINT [PK_CatalogSectionVendor_CatalogId_SectionId] PRIMARY KEY CLUSTERED ([catalogId], [sectionId], [vendorId]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendor] ADD CONSTRAINT [catalogSectionVendor_UNIQ] UNIQUE NONCLUSTERED ([catalogId], [sectionId], [vendorId]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendor] ADD CONSTRAINT [FK_CatalogSectionVendor_CatalogSection] FOREIGN KEY ([catalogId], [sectionId]) REFERENCES [dbo].[CatalogSection] ([catalogId], [sectionId]) +GO +ALTER TABLE [dbo].[CatalogSectionVendor] ADD CONSTRAINT [FK_CatalogSectionVendor_Vendor] FOREIGN KEY ([vendorId]) REFERENCES [dbo].[Vendor] ([vendorId]) +GO Index: Tables/dbo.CatalogSectionVendorHistory.sql =================================================================== diff -u --- Tables/dbo.CatalogSectionVendorHistory.sql (revision 0) +++ Tables/dbo.CatalogSectionVendorHistory.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,40 @@ +CREATE TABLE [dbo].[CatalogSectionVendorHistory] +( +[catalogId] [int] NOT NULL, +[sectionId] [int] NOT NULL, +[vendorId] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[vendorPageNumber] [int] NOT NULL, +[writerId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[photoCoOrdinatorId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[calReceivedDate] [datetime] NULL, +[calcsStartDate] [datetime] NULL, +[calcsDueDate] [datetime] NULL, +[revisedDate] [datetime] NULL, +[writingStartDate] [datetime] NULL, +[writingDoneDate] [datetime] NULL, +[pcProofing] [datetime] NULL, +[prooferId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[proofStartDate] [datetime] NULL, +[proofDoneDate] [datetime] NULL, +[pagCheckInDate] [datetime] NULL, +[statusCodeId] [int] NOT NULL CONSTRAINT [DF_CatalogSectionVendorHistory_statusCodeId] DEFAULT ((39300)), +[writingDueDate] [datetime] NULL, +[photoDueDate] [datetime] NULL, +[sectionVendorStartDate] [datetime] NULL, +[sectionVendorDueDate] [datetime] NULL, +[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[dateCreated] [datetime] NOT NULL CONSTRAINT [DF_CatalogSectionVendorHistory_dateCreated] DEFAULT (getdate()), +[createdBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CatalogSectionVendorHistory_createdBy] DEFAULT (suser_sname()), +[lastUpdated] [datetime] NOT NULL CONSTRAINT [DF_CatalogSectionVendorHistory_lastUpdated] DEFAULT (getdate()), +[lastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CatalogSectionVendorHistory_lastUpdatedBy] DEFAULT (suser_sname()), +[dateDeleted] [datetime] NULL, +[deletedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[version] [int] NOT NULL CONSTRAINT [DF_CatalogSectionVendorHistory_version] DEFAULT ((0)) +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendorHistory] ADD CONSTRAINT [PK_CatalogSectionVendorHistory_CSVIds] PRIMARY KEY CLUSTERED ([catalogId], [sectionId], [vendorId], [lastUpdated]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendorHistory] ADD CONSTRAINT [catalogSectionVendorHistory_UNIQ] UNIQUE NONCLUSTERED ([catalogId], [sectionId], [vendorId], [lastUpdated]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendorHistory] ADD CONSTRAINT [FK_CatalogSectionVendorHistory_CatalogSectionVendor] FOREIGN KEY ([catalogId], [sectionId], [vendorId]) REFERENCES [dbo].[CatalogSectionVendor] ([catalogId], [sectionId], [vendorId]) +GO Index: Tables/dbo.CatalogSectionVendorPart.sql =================================================================== diff -u --- Tables/dbo.CatalogSectionVendorPart.sql (revision 0) +++ Tables/dbo.CatalogSectionVendorPart.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,26 @@ +CREATE TABLE [dbo].[CatalogSectionVendorPart] +( +[catalogId] [int] NOT NULL, +[sectionId] [int] NOT NULL, +[vendorId] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[partNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[isRevised] [bit] NOT NULL CONSTRAINT [DF__CatalogSe__isRev__1AD3FDA4] DEFAULT ((0)), +[isApproved] [bit] NOT NULL CONSTRAINT [DF__CatalogSe__isApp__1BC821DD] DEFAULT ((0)), +[revisionReasonCodeId] [int] NULL, +[retailPrice] [decimal] (8, 3) NULL, +[baseDealerPrice] [decimal] (8, 3) NULL, +[dateCreated] [datetime] NOT NULL, +[createdBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[lastUpdated] [datetime] NOT NULL, +[lastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[dateDeleted] [datetime] NULL, +[deletedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[version] [int] NOT NULL +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendorPart] ADD CONSTRAINT [PK_CatalogSectionVendorPart_AllIds] PRIMARY KEY CLUSTERED ([catalogId], [sectionId], [vendorId], [partNumber]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendorPart] ADD CONSTRAINT [catalogSectionVendorPart_UNIQ] UNIQUE NONCLUSTERED ([catalogId], [sectionId], [vendorId], [partNumber], [dateDeleted]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[CatalogSectionVendorPart] ADD CONSTRAINT [FK_CatalogSectionVendorPart_CatalogSectionVendor] FOREIGN KEY ([catalogId], [sectionId], [vendorId]) REFERENCES [dbo].[CatalogSectionVendor] ([catalogId], [sectionId], [vendorId]) +GO Index: Tables/dbo.DueDates.sql =================================================================== diff -u --- Tables/dbo.DueDates.sql (revision 0) +++ Tables/dbo.DueDates.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,26 @@ +CREATE TABLE [dbo].[DueDates] +( +[catalogId] [int] NOT NULL, +[sectionId] [int] NOT NULL, +[vendorId] [int] NOT NULL, +[dueDateTypeId] [int] NOT NULL, +[isRevision] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[startDate] [datetime] NOT NULL, +[endDate] [datetime] NOT NULL, +[revisionReasonCodeId] [int] NULL, +[isNotified] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[notificationId] [int] NULL, +[comment] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[dateCreated] [datetime] NOT NULL, +[createdBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[lastUpdated] [datetime] NOT NULL, +[lastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[dateDeleted] [datetime] NULL, +[deletedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[version] [int] NOT NULL +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[DueDates] ADD CONSTRAINT [PK_DueDates_CatalogId_SectionId_VendorId] PRIMARY KEY CLUSTERED ([catalogId], [sectionId], [vendorId], [dueDateTypeId], [isRevision], [startDate], [endDate]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[DueDates] ADD CONSTRAINT [FK_DueDates_Catalog] FOREIGN KEY ([catalogId]) REFERENCES [dbo].[Catalog] ([catalogId]) +GO Index: Tables/dbo.Name.sql =================================================================== diff -u --- Tables/dbo.Name.sql (revision 0) +++ Tables/dbo.Name.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,24 @@ +CREATE TABLE [dbo].[Name] +( +[nameId] [int] NOT NULL IDENTITY(1, 1), +[globalId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[nameCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[firstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[lastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[mainFrameNameId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[isWriter] [bit] NOT NULL CONSTRAINT [DF__Name__isWriter__2645B050] DEFAULT ((0)), +[isPhotoCoOrdinator] [bit] NOT NULL CONSTRAINT [DF__Name__isPhotoCoO__2739D489] DEFAULT ((0)), +[isAdministrator bit NOT NULL DEFAULT 0, + [dateCreated] [datetime] NOT NULL, +[createdBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[lastUpdated] [datetime] NOT NULL, +[lastUpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[dateDeleted] [datetime] NULL, +[deletedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[version] [int] NOT NULL +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[Name] ADD CONSTRAINT [PK_Names_nameId] PRIMARY KEY CLUSTERED ([globalId], [firstName], [lastName]) ON [PRIMARY] +GO +ALTER TABLE [dbo].[Name] ADD CONSTRAINT [Name_UNIQ] UNIQUE NONCLUSTERED ([nameId], [firstName], [lastName], [dateDeleted]) ON [PRIMARY] +GO Index: Tables/dbo.Vendor.sql =================================================================== diff -u --- Tables/dbo.Vendor.sql (revision 0) +++ Tables/dbo.Vendor.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,31 @@ +CREATE TABLE [dbo].[Vendor] +( +[vendorId] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[vendorName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, +[vendorAddress1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[vendorAddress2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[vendorCity] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[vendorState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[vendorZip] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[vendorPhone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[vendorContact] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[buyerLocation] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[buyer] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[buyerConcat] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[pickId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[pickName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[vendorStatus] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[discontinuedDate] [datetime] NULL, +[inactiveDate] [datetime] NULL, +[buyerId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[buyerName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[buyerGroup] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[createDate] [datetime] NULL, +[updateDate] [datetime] NULL, +[isBuyer] [bit] NULL, +[buyerManagerGlobalId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, +[buyerGlobalId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL +) ON [PRIMARY] +GO +ALTER TABLE [dbo].[Vendor] ADD CONSTRAINT [PK_Vendor] PRIMARY KEY CLUSTERED ([vendorId]) ON [PRIMARY] +GO Index: Views/dbo.vwCatalog.sql =================================================================== diff -u --- Views/dbo.vwCatalog.sql (revision 0) +++ Views/dbo.vwCatalog.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,51 @@ +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO +CREATE VIEW [dbo].[vwCatalog] +AS +SELECT + C.[catalogId] + , C.[year] + , C.[catalogName] + , C.[regionId] + , 'regionCode' = X.[typeCode] + , 'regionDescr' = X.[typeDescr] + , C.[catalogCodeId] + , 'catalogCode' = Y.[typeCode] + , 'catalogCodeDescr' = Y.[typeDescr] + , C.[catalogTypeId] + , 'catalogTypeCode' = Z.[typeCode] + , 'catalogTypeDescr' = Z.[typeDescr] + , C.[statusCodeId] + , 'catalogStatusCode' = W.[typeCode] + , 'catalogStatusCodeDescr' = W.[typeDescr] + , C.[mainframeCatalogCode] + , 'catalogDueDate' = [dueDate] + , C.[dateCreated] + , C.[createdBy] + , C.[lastUpdated] + , C.[lastUpdatedBy] + , C.[dateDeleted] + , C.[deletedBy] + , C.[version] +FROM dbo.Catalog C + INNER JOIN dbo.TypeXref X + ON C.regionId = X.typeId + AND X.type = 'Region' + AND X.dateDeleted IS NULL + INNER JOIN dbo.TypeXref Y + ON C.catalogCodeId = Y.typeId + AND Y.type = 'CatalogCode' + AND Y.dateDeleted IS NULL + LEFT JOIN dbo.TypeXref Z + ON C.catalogTypeId = Z.typeId + AND Z.type = 'CatalogType' + AND Z.dateDeleted IS NULL + LEFT JOIN dbo.TypeXref W + ON C.statusCodeId = W.typeId + AND W.type = 'CatalogStatusCode' + AND W.dateDeleted IS NULL +WHERE C.dateDeleted IS NULL + +GO Index: Views/dbo.vwCatalogSection.sql =================================================================== diff -u --- Views/dbo.vwCatalogSection.sql (revision 0) +++ Views/dbo.vwCatalogSection.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,33 @@ +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO +CREATE VIEW [dbo].[vwCatalogSection] +AS +SELECT CS.[catalogId] + , CS.[sectionId] + , CS.[sectionName] + , CS.[startPageNumber] + , CS.[sectionNumber] + , CS.[statusCodeId] + , 'catalogSectionStatusCode' = X.[typeCode] + , 'catalogSectionStatusCodeDescr' = X.[typeDescr] + , CS.[calcsStartDate] + , CS.[calcsDueDate] + , CS.[writingDueDate] + , CS.[photoDueDate] + , 'catalogSectionDueDate' = CS.[dueDate] + , CS.[dateCreated] + , CS.[createdBy] + , CS.[lastUpdated] + , CS.[lastUpdatedBy] + , CS.[dateDeleted] + , CS.[deletedBy] + , CS.[version] +FROM [dbo].[CatalogSection] CS + LEFT JOIN dbo.TypeXref X + ON CS.statusCodeId = X.typeId + AND X.type = 'CatalogStatusCode' + AND X.dateDeleted IS NULL +WHERE CS.dateDeleted IS NULL +GO Index: Views/dbo.vwPart.sql =================================================================== diff -u --- Views/dbo.vwPart.sql (revision 0) +++ Views/dbo.vwPart.sql (revision bb48ae6de08e887bc1dfcef944040def9567e853) @@ -0,0 +1,59 @@ +SET QUOTED_IDENTIFIER ON +GO +SET ANSI_NULLS ON +GO +CREATE VIEW [dbo].[vwPart] +AS +SELECT + [locale], + [partNumber], + [partLocaleId], + [punctuatedPartNumber], + [partDescr], + [marketingDescr], + [partStatus], + [isActive], + [brandId], + [brandCode], + [brandName], + [brandPrimaryMediaId], + [brandMediaTypeId], + [brandPrefixUrl], + [brandAssetPath], + [brandMediaUrl], + [brandExtension], + [brandMediaDescription], + [brandWebsiteUrl], + [carbWebsiteUrl], + [vendorId], + [vendorName], + [vendorPartNumber], + [vendorPunctuatedPartNumber], + [secondaryVendorId], + [secondaryVendorName], + [warehouseCountry], + [upc], + [isNew], + [isHidden], + [isWebBlocked], + [webBlockedCode], + [webBlockedDescr], + [baseDealerPrice], + [retailPrice] + [primaryMediaId], + [mediaTypeId], + [prefixUrl], + [assetPath], + [mediaUrl], + [extension], + [mediaDescription] + [expectedAvailDate], + [peAvail], + [dateDiscontinued], + [tag], + [lastModified] + [isDigital] +FROM Parts_US.dbo.Part +GO +GRANT SELECT ON [dbo].[vwPart] TO [calcpro_user] +GO