Public feed
#603920
DELETE FROM ProjectSpecificationMatrixGODELETE FROM SpecificationMatrixDataColumnLayoutGODELETE FROM TestColumnObjectLayoutGODELETE FROM SpecificationTestColumnLayoutGODELETE FROM SpecificationMatrixTemplateLayoutGODELETE FROM TestColumnObjectDataGODELETE FROM TestColumnObjectGODELETE FROM SpecificationTestColumnGODELETE FROM SpecificationMatrixDataColumnSpecificationGODELETE FROM SpecificationMatrixDataColumnGODELETE FROM SpecificationMatrixSpecificationGODELETE FROM SpecificationGODELETE FROM SpecificationMatrixGODELETE FROM TracerTestGODELETE FROM WeldTestGODELETE FROM WeldActivityLogGODELETE FROM WeldGODELETE FROM ProcedureQualificationWeldProcedureGODELETE FROM WeldProcedureGODELETE FROM WeldTestTypeGODELETE FROM WeldStatusGODELETE FROM WelderProcedureQualificationRecordGODELETE FROM ProcedureQualificationGODELETE FROM WelderGODELETE FROM ProjectEmployeeGODELETE FROM EmployeeGODELETE FROM EmployeePositionGODELETE FROM WeldTypeGODELETE FROM HydroTestDrawingGODELETE FROM HydroTestSpoolGODELETE FROM HydroTestGODELETE FROM HydroMediumGODELETE FROM TestResultGODELETE FROM SpoolRevisionGODELETE FROM SpoolGODELETE FROM EmployeeGO--ALTER TABLE DrawingRevision DROP CONSTRAINT Drawing_DrawingRevision_FK1--GO--ALTER TABLE Drawing DROP CONSTRAINT DrawingRevision_Drawing_FK1--GODELETE FROM DrawingRevisionGODELETE FROM DrawingGODELETE FROM DrawingTypeGODELETE FROM ProjectGODELETE FROM CustomerGO/** Reset Identity Values **/DBCC CHECKIDENT (Project, RESEED, 0)DBCC CHECKIDENT (Customer, RESEED, 0)DBCC CHECKIDENT (WeldProcedure, RESEED, 0)DBCC CHECKIDENT (WeldTestType, RESEED, 0)DBCC CHECKIDENT (SpecificationMatrixTemplateLayout, RESEED, 0)DBCC CHECKIDENT (SpecificationTestColumnLayout, RESEED, 0)DBCC CHECKIDENT (TestColumnObjectLayout, RESEED, 0)DBCC CHECKIDENT (SpecificationMatrixDataColumnLayout, RESEED, 0)DBCC CHECKIDENT (SpecificationMatrix, RESEED, 0)DBCC CHECKIDENT (SpecificationMatrixDataColumn, RESEED, 0)DBCC CHECKIDENT (Specification, RESEED, 0)DBCC CHECKIDENT (SpecificationTestColumn, RESEED, 0)DBCC CHECKIDENT (TestColumnObject, RESEED, 0)DBCC CHECKIDENT (WeldTest, RESEED, 0)DBCC CHECKIDENT (TracerTest, RESEED, 0)DBCC CHECKIDENT (WelderProcedureQualificationRecord, RESEED, 0)DBCC CHECKIDENT (ProcedureQualification,RESEED, 0)DBCC CHECKIDENT (WeldStatus, RESEED, 0)DBCC CHECKIDENT (WeldActivityLog, RESEED, 0)DBCC CHECKIDENT (WeldProcedure, RESEED, 0)DBCC CHECKIDENT (Weld, RESEED, 0)DBCC CHECKIDENT (WeldType, RESEED, 0)DBCC CHECKIDENT (TestResult, RESEED, 0)DBCC CHECKIDENT (HydroMedium, RESEED, 0)DBCC CHECKIDENT (HydroTest, RESEED, 0)DBCC CHECKIDENT (Spool, RESEED, 0)DBCC CHECKIDENT (Drawing, RESEED, 0)DBCC CHECKIDENT (DrawingType, RESEED, 0)DBCC CHECKIDENT (EmployeePosition, RESEED, 0)DBCC CHECKIDENT (Employee, RESEED, 0)/** Supporting Tables **//** Customer **/INSERT INTO Customer (CustomerName) VALUES ('Cord Projects')GOINSERT INTO Customer (CustomerName) VALUES ('Epcor')GOINSERT INTO Customer (CustomerName) VALUES ('Syncrude')GOINSERT INTO Customer (CustomerName) VALUES ('Suncor')GOINSERT INTO Customer (CustomerName) VALUES ('Nexen')GO/** SpecificationMatrix **/INSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-825 NEXEN','B31.3')GOINSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-826 NEXEN','B31.3')GOINSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-827 NEXEN','B31.3')GOINSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-828 NEXEN','B31.3')GOINSERT INTO SpecificationMatrix (SpecMatrixId, SpecificationCode) VALUES ('CPL-829 NEXEN','B31.3')GO/** WeldTestType **/INSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Radiography Test','RT')GOINSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Brinnel Hardness Test','BHN')GOINSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Magnetic Particle Inspection','MPI')GOINSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Liquid Penetrant Inspection','LPI')GOINSERT INTO WeldTestType (TestTypeName, Abbreviation) VALUES ('Ferrite Test','FT')GO/** SpecificationMatrixTemplateLayout **/INSERT INTO SpecificationMatrixTemplateLayout (SpecificationCodeLayout) VALUES ('B31.3')GOINSERT INTO SpecificationMatrixTemplateLayout (SpecificationCodeLayout) VALUES ('Z662')GO/** DrawingType **/INSERT INTO DrawingType (DrawingTypeName) VALUES ('Module Work Package')GOINSERT INTO DrawingType (DrawingTypeName) VALUES ('Construction Work Package')GOINSERT INTO DrawingType (DrawingTypeName) VALUES ('P&ID')GOINSERT INTO DrawingType (DrawingTypeName) VALUES ('Isometric')GO/** EmployeePosition **/INSERT INTO EmployeePosition (PositionName) VALUES ('Welder')GOINSERT INTO EmployeePosition (PositionName) VALUES ('Project Manager')GOINSERT INTO EmployeePosition (PositionName) VALUES ('Turnover Co-Ordinator')GOINSERT INTO EmployeePosition (PositionName) VALUES ('Quality Administrator')GOINSERT INTO EmployeePosition (PositionName) VALUES ('Quality Services Manager')GOINSERT INTO EmployeePosition (PositionName) VALUES ('Office Administrator')GOINSERT INTO EmployeePosition (PositionName) VALUES ('Document Control')GOINSERT INTO EmployeePosition (PositionName) VALUES ('Data Administration Clerk')GO/** HydroMedium **/INSERT INTO HydroMedium (Medium) VALUES ('Water')GOINSERT INTO HydroMedium (Medium) VALUES ('Glycol')GO/** TestResult **/INSERT INTO TestResult (Result) VALUES ('Pass')GOINSERT INTO TestResult (Result) VALUES ('Fail')GOINSERT INTO TestResult (Result) VALUES ('Pending')GO/** WeldType **/INSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Socket Weld', 'SW')GOINSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Gap Shot', 'GS')GOINSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Butt Weld', 'BW')GOINSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Threadolet', 'TH')GOINSERT INTO WeldType (WeldTypeName, Abbreviation) VALUES ('Dummy Leg', 'DL')GO/** WeldStatus **/INSERT INTO WeldStatus (StatusName) VALUES ('Not Started')GOINSERT INTO WeldStatus (StatusName) VALUES ('Pending')GOINSERT INTO WeldStatus (StatusName) VALUES ('Complete')GOINSERT INTO WeldStatus (StatusName) VALUES ('Reweld')GO/** Employee **/INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Jeff', 'O''Toole', 1) /** 1 **/GOINSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Trent', 'Sohnle', 1) /** 2 **/GOINSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Andrij', 'Demianczuk', 1) /** 3 **/GOINSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Sean', 'Howat', 1) /** 4 **/GO INSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Alvar', 'Lindroth', 2)GOINSERT INTO Employee (FirstName, LastName, EmployeePositionId) VALUES ('Kara', 'Fontaine', 3)GO/** ProcedureQualification **/INSERT INTO ProcedureQualification (QualificationName) VALUES ('W-02252')GOINSERT INTO ProcedureQualification (QualificationName) VALUES ('W-5545')GOINSERT INTO ProcedureQualification (QualificationName) VALUES ('W-5545')GOINSERT INTO ProcedureQualification (QualificationName) VALUES ('W-21786')GOINSERT INTO ProcedureQualification (QualificationName) VALUES ('W-6186')GO/** Child Tables **//** Project **/INSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('Epcor Pass Route',1, 1)GOINSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('BP Pipelines Pass Route',2,1)GOINSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('Syncrude Pass Route',3,1)GOINSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('Suncor Pass Route',4,1)GOINSERT INTO Project (ProjectName, CustomerId, Status) VALUES ('NEXEN Pass Route',5,1)GO/** Drawing **/INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-006', 1)/** 1 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-016', 1)/** 2 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-017', 1)/** 3 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-120', 1)/** 4 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-121', 1)/** 5 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-122', 1)/** 6 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-123', 1)/** 7 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-124', 1)/** 8 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-125', 1)/** 9 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (1, '7700-PRM-126', 1)/** 10 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-127', 1)/** 11 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId) VALUES (2, '8100-PRM-128', 1)/** 12 **/GO/** Drawing Children **/INSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (3, '8101-PRM-400', 1, 1, 0)/** 13 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (3, '8102-PRM-401', 1, 1, 0)/** 14 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-402', 1, 13, 0)/** 15 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-403', 1, 13, 0)/** 16 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-404', 1, 13, 0)/** 17 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-405', 1, 13, 0)/** 18 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-406', 1, 13, 0)/** 19 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-407', 1, 13, 0)/** 20 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8101-PRM-408', 1, 13, 0)/** 21 **/GOINSERT INTO Drawing (DrawingTypeId, InternalDrawingNumber, ProjectId, ParentDrawingId, ParentRevisionNumber) VALUES (4, '8102-PRM-409', 1, 13, 0)/** 22 **/GO/** Spool **/INSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('A', '8100-PRM-021', '003035', 3, 0 )GOINSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('A', '8100-PRM-021', '003035', 3, 0 )GOINSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('A', '8100-PRM-021', '003035', 3, 0 )GOINSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('B', '8100-PRM-021', '003036', 3, 0 )GOINSERT INTO Spool (Spoolno, ModuleId, ControlNumber, DrawingId, DrawingRevisionNumber) VALUES ('B', '8100-PRM-021', '003036', 3, 0 )GO/** ProjectSpecificationMatrix **/INSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (1,1)GOINSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (2,2)GOINSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (3,3)GOINSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (4,4)GOINSERT INTO ProjectSpecificationMatrix (ProjectId, SpecificationMatrixId) VALUES (5,5)GO/** WeldProcedure **/INSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-6', 1)GOINSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-7', 1)GOINSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-14A', 1)GOINSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-15', 1)GOINSERT INTO WeldProcedure (ProcedureName, CustomerId) VALUES ('CPL-16', 1)GO/** Specification **/INSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (1, 'A')GOINSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (2, 'A')GOINSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (1, 'AC')GOINSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (2, 'AC')GOINSERT INTO Specification (WeldProcedureId, PipeClass) VALUES (3, 'AD')GO/** SpecificationMatrixSpecification **/INSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,1)GOINSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,2)GOINSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,3)GOINSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,4)GOINSERT INTO SpecificationMatrixSpecification (SpecificationMatrixId, SpecificationId) VALUES (1,5)GO/** SpecificationMatrixDataColumnLayout **/INSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'Pipe Class')GOINSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'Cord WPS')GOINSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'P#')GOINSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'Material Type')GOINSERT INTO SpecificationMatrixDataColumnLayout (TemplateIdLayout, NameLayout) VALUES (1,'WPS max W.T')GO/** SpecificationTestColumnLayout **/INSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (1,1)GOINSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (2,1)GOINSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (3,1)GOINSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (4,1)GOINSERT INTO SpecificationTestColumnLayout (WeldTestTypeId, TemplateIdLayout) VALUES (5,1)GO/** TestColumnObjectLayout **/INSERT INTO TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (1, 'BW', 1)GOINSERT INTO TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (1, 'SYN/MFG Service', 0)GOINSERT INTO TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (1, 'SW Gap Shot', 1)GOINSERT INTO TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (3, 'SW / Olets', 1)GOINSERT INTO TestColumnObjectLayout (SpecificationTestColumnIdLayout, NameLayout, IsObjectWeldLayout) VALUES (3, 'ATT', 1)GO/** SpecificationTestColumn **/INSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (1,1)GOINSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (2,1)GOINSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (3,1)GOINSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (4,1)GOINSERT INTO SpecificationTestColumn (WeldTestTypeId, SpecificationMatrixId) VALUES (5,1)GO/** TestColumnObject **/INSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (1, 'BW', 1)GOINSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (2, 'SYN/MFG Service', 0)GOINSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (3, 'SW Gap Shot', 1)GOINSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (4, 'SW/Olets', 1)GOINSERT INTO TestColumnObject (SpecificationTestColumnId, Name, IsObjectWeld) VALUES (5, 'ATT', 0)GO/** TestColumnObjectData **/INSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (1,1, '10')GOINSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (2,1, '5')GOINSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (3,1, '10')GOINSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (4,1, '100')GOINSERT INTO TestColumnObjectData (TestColumnObjectId, SpecificationId, Data) VALUES (5,1, 'N/A')GO/** SpecificationMatrixDataColumn **/INSERT INTO SpecificationMatrixDataColumn (Name, SpecificationMatrixId) VALUES ('Weld Process', 1)GOINSERT INTO SpecificationMatrixDataColumn (Name, SpecificationMatrixId) VALUES ('Mat. Type', 1)GOINSERT INTO SpecificationMatrixDataColumn (Name, SpecificationMatrixId) VALUES ('Preheat', 1)GO/** SpecificationMatrixDataColumnSpecification **/INSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (1,1,'P1')GOINSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (1,2,'P1')GOINSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (1,3,'P1')GOINSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (2,1,'A106 Gr B')GOINSERT INTO SpecificationMatrixDataColumnSpecification (SpecificationMatrixDataColumnId, SpecificationId, SpecificationData) VALUES (2,2,'A106 Gr B')GO/** Weld **/INSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A1', 1, 1, 0, 32.2, 'Std(.154)', 'TRACER', getDate(), 'A', 13, 0)GOINSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A2', 2, 1, 0, 32.2, 'Std(.154)', getDate(), 'B', 13, 0)GOINSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A3', 3, 2, 0, 30, 'Std(.154)', getDate(), 'C', 13, 0)GOINSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A4', 1, 2, 0, 30.5, 'Std(.154)', getDate(), 'D', 13, 0)GOINSERT INTO Weld (WeldTypeId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 'A1', 2, 2, 0, 22, 'Std(.154)', 'TRACER', getDate(), 'E', 13, 0)GO/** Weld With ParentId **/INSERT INTO Weld (WeldTypeId, ParentId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (1, 1, 'A1', 2, 2, 0, 22, 'Std(.154)', 'REWELD', getDate(), 'E', 13, 0)GOINSERT INTO Weld (WeldTypeId, ParentId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (2, 2, 'A2', 2, 2, 0, 22, 'Std(.154)', 'REWELD', getDate(), 'E', 13, 0)GOINSERT INTO Weld (WeldTypeId, ParentId, WelderId, WeldProcedureId, SpoolId, SpoolRevisionNumber, Size, Wall, Comment, WeldDate, WeldTag, DrawingId, DrawingRevisionNumber) VALUES (3, 3, 'A3', 2, 2, 0, 22, 'Std(.154)', 'REWELD', getDate(), 'E', 13, 0)GO/** WelderProcedureQualificationRecord **/INSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (1, getDate(), 'A1')GOINSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (1, getDate(), 'A2')GOINSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (2, getDate(), 'A3')GOINSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (3, getDate(), 'A4')GOINSERT INTO WelderProcedureQualificationRecord (ProcedureQualificationId, QualificationExpiryDate, WelderId) VALUES (4, getDate(), 'A1')GO/** WeldTest **/INSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (1, 1, getDate(), 2)GOINSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (2, 1, getDate(), 2)GOINSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (3, 2, getDate(), 2)GOINSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (4, 2, getDate(), 1)GOINSERT INTO WeldTest (WeldId, WeldTestTypeId, TestDate, TestResultId) VALUES (5, 3, getDate(), 3)GO/** Tracertest **/INSERT INTO TracerTest (WeldTestId, WeldId) VALUES (1, 2)GOINSERT INTO TracerTest (WeldTestId, WeldId) VALUES (1, 3)GOINSERT INTO TracerTest (WeldTestId, WeldId) VALUES (2, 4)GOINSERT INTO TracerTest (WeldTestId, WeldId) VALUES (2, 5)GOINSERT INTO TracerTest (WeldTestId, WeldId) VALUES (3, 6)GO/** ProcedureQualificationWeldProcedure **/INSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (1, 1)GOINSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (2, 1)GOINSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (3, 1)GOINSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (4, 4)GOINSERT INTO ProcedureQualificationWeldProcedure (ProcedureQualificationId, WeldProcedureId) VALUES (5, 4)GO/** WeldActivityLog **/INSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (1, 2, getDate())GOINSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (2, 1, getDate())GOINSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate, Comment) VALUES (3, 1, getDate(), 'THIS IS A TEST')GOINSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (4, 2, getDate())GOINSERT INTO WeldActivityLog (WeldId, WeldStatusId, LogDate) VALUES (5, 3, getDate())GO/** ProjectEmployee **/INSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (1, 1)GOINSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (1, 2)GOINSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (1, 3)GOINSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (2, 4)GOINSERT INTO ProjectEmployee (ProjectId, EmployeeId) VALUES (2, 2)GO/** HydroTest **/INSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (2, getDate(), 1)GOINSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (2, getDate(), 1)GOINSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (2, getDate(), 1)GOINSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (1, getDate(), 1)GOINSERT INTO HydroTest (TestResultId, TestDate, HydroMediumId) VALUES (1, getDate(), 2)GO/** HydroTestSpool **/INSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (1, 1)GOINSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (2, 2)GOINSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (3, 3)GOINSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (4, 4)GOINSERT INTO HydroTestSpool (HydroTestId, SpoolId) VALUES (5, 5)GO/** HydroTestDrawing **/INSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (1, 13)GOINSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (1, 14)GOINSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (2, 15)GOINSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (2, 16)GOINSERT INTO HydroTestDrawing (HydroTestId, DrawingId) VALUES (3, 17)GO/* Create procedure/function _DrawingRevision_GetNextDrawingRevisionNumber. *//** Work Package Revision **/CREATE PROCEDURE _DrawingRevision_GetNextDrawingRevisionNumber( @DrawingId int = NULL )AS SET NOCOUNT ON DECLARE @NewDrawingRevisionNumber int BEGIN IF @DrawingId IS NULL BEGIN RAISERROR ('_DrawingRevision_GetNextDrawingRevisionNumber :: DrawingId must be assigned a value and cannot be null', 16, 1) END ELSE BEGIN IF Exists( SELECT MAX(DR.DrawingRevisionNumber) FROM DrawingRevision DR WHERE DR.DrawingId = @DrawingId ) BEGIN SET @NewDrawingRevisionNumber = ( SELECT MAX(DR.DrawingRevisionNumber) + 1 FROM DrawingRevision DR WHERE DR.DrawingId = @DrawingId ) END -- END IF END -- END IF ENDRETURN @NewDrawingRevisionNumberGOgo/* Create procedure/function _DrawingRevision_InsertNewRevision. */CREATE PROCEDURE _DrawingRevision_InsertNewRevision(@DrawingId int = NULL)AS SET NOCOUNT ON DECLARE @NewDrawingRevisionNumber int BEGIN IF @DrawingId IS NULL BEGIN RAISERROR ('_DrawingRevision_InsertNewRevision :: DrawingId must be assigned a value and cannot be null', 16, 1) END ELSE BEGIN EXEC @NewDrawingRevisionNumber = _DrawingRevision_GetNextDrawingRevisionNumber @DrawingId IF @NewDrawingRevisionNumber IS NULL BEGIN RAISERROR ('_DrawingRevision_InsertNewRevision :: NewDrawingRevisionNumber was returned as null from procedure _DrawingRevision_GetNextDrawingRevisionNumber', 16, 1) END ELSE BEGIN -- Do the Insert All is Good INSERT INTO DrawingRevision (DrawingId, DrawingRevisionNumber) VALUES (@DrawingId, @NewDrawingRevisionNumber) IF @@ERROR <> 0 BEGIN RAISERROR ('_DrawingRevision_InsertNewRevision :: There was an issue while inserting new Drawing Revision', 16, 1) END -- END IF END -- END IF END -- END IF ENDRETURNGOgo/* Create procedure/function _SpoolRevision_GetNextSpoolRevisionNumber. *//** Spool Revision **/CREATE PROCEDURE _SpoolRevision_GetNextSpoolRevisionNumber( @SpoolId int = NULL )AS SET NOCOUNT ON DECLARE @NewSpoolRevisionNumber int BEGIN IF @SpoolId IS NULL BEGIN RAISERROR ('_SpoolRevision_GetNextSpoolRevisionNumber :: SpoolId must be assigned a value and cannot be null', 16, 1) END ELSE BEGIN IF Exists( SELECT MAX(IR.SpoolRevisionNumber) FROM SpoolRevision IR WHERE IR.SpoolId = @SpoolId ) BEGIN SET @NewSpoolRevisionNumber = ( SELECT MAX(IR.SpoolRevisionNumber) + 1 FROM SpoolRevision IR WHERE IR.SpoolId = @SpoolId ) END -- END IF END -- END IF ENDRETURN @NewSpoolRevisionNumberGOgo/* Create procedure/function _SpoolRevision_InsertNewRevision. */CREATE PROCEDURE _SpoolRevision_InsertNewRevision(@SpoolId int = NULL)ASSET NOCOUNT ON DECLARE @NewSpoolRevisionNumber int BEGIN IF @SpoolId IS NULL BEGIN RAISERROR ('_SpoolRevision_InsertNewRevision :: SpoolId must be assigned a value and cannot be null', 16, 1) END ELSE BEGIN EXEC @NewSpoolRevisionNumber = _SpoolRevision_GetNextSpoolRevisionNumber @SpoolId IF @NewSpoolRevisionNumber = NULL BEGIN RAISERROR ('_SpoolRevision_InsertNewRevision :: NewSpoolRevisionNumber was returned as null from procedure _SpoolRevision_GetNextSpoolRevisionNumber', 16, 1) END ELSE BEGIN -- Do the Insert All is Good INSERT INTO SpoolRevision (SpoolId, SpoolRevisionNumber) VALUES (@SpoolId, @NewSpoolRevisionNumber) IF @@ERROR <> 0 BEGIN RAISERROR ('_SpoolRevision_InsertNewRevision :: There was an issue while inserting new Work Package Revision', 16, 1) END -- END IF END -- END IF END -- END IF ENDRETURNGOgo/* Create procedure/function _Welder_GetNextNewWelder. */CREATE PROCEDURE _Welder_GetNextNewWelder ( @WelderId varchar(3) = NULL OUTPUT, @WelderIdPrefix char(1) = NULL )AS SET NOCOUNT ON DECLARE @WelderIdSuffix int, @WelderIdCounter int BEGIN -- TODO -- Check to see if Temp Table Will Exist On Recursive Call CREATE TABLE #WelderRange ( WelderIdSuffix int ) -- Lets Load Our Pretty Table SET @WelderIdCounter = 1 WHILE @WelderIdCounter < 100 BEGIN INSERT INTO #WelderRange ( WelderIdSuffix ) VALUES ( @WelderIdCounter ) SET @WelderIdCounter = @WelderIdCounter + 1 END -- END WHILE -- Now Lets Set Our Prefix IF @WelderIdPrefix IS NULL BEGIN SELECT @WelderIdPrefix = 'A' END -- END IF SET @WelderIdSuffix = ( SELECT min(WelderIdSuffix) FROM #WelderRange WHERE WelderIdSuffix NOT IN ( SELECT cast( substring(WelderId, 2, len(WelderId) - 1) AS int ) FROM Welder WHERE substring(WelderId, 1, 1) = @WelderIdPrefix ) ) IF @WelderIdSuffix IS NULL BEGIN IF @WelderIdPrefix = 'Z' BEGIN RAISERROR('This system has exceeded the number of possible WelderId combinations which is 2574, plese consult the System Designer',16 ,1) END ELSE BEGIN SET @WelderIdPrefix = cast( ascii(@WelderIdPrefix) + 1 AS char) EXEC _Welder_GetNextNewWelder @WelderId OUTPUT, @WelderIdPrefix END -- END IF END ELSE BEGIN SET @WelderId = ( SELECT @WelderIdPrefix + cast(@WelderIdSuffix AS char) ) END -- END IF ENDGOgo/* Create procedure/function FN_Spool_CountWeldStatus. */CREATE FUNCTION FN_Spool_CountWeldStatus ( @SpoolId int, @WeldStatusId int )RETURNS intAS BEGIN DECLARE @TotalActiveWeldCount int SET @TotalActiveWeldCount = ( SELECT count(W.WeldId) FROM Weld W WHERE W.SpoolId = @SpoolId AND W.WeldId = ( SELECT TOP 1 WAL.WeldId FROM WeldActivityLog WAL WHERE WAL.WeldId = W.WeldId AND WAL.WeldStatusId = @WeldStatusId ORDER BY LogDate DESC ) ) RETURN @TotalActiveWeldCount ENDGOgo/* Create procedure/function _Spool_CountWeldStatus. */CREATE PROCEDURE _Spool_CountWeldStatus ( @SpoolId int, @WeldStatusId int )AS SET NOCOUNT ON DECLARE @TotalWeldCount int BEGIN SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Spool_CountWeldStatus (@SpoolId, @WeldStatusId) ) RETURN @TotalWeldCount ENDGOgo/* Create procedure/function FN_Spool_CountNotWeldStatus. */CREATE FUNCTION FN_Spool_CountNotWeldStatus ( @SpoolId int, @WeldStatusId int )RETURNS intAS BEGIN DECLARE @TotalActiveWeldCount int SET @TotalActiveWeldCount = ( SELECT count(W.WeldId) FROM Weld W WHERE W.SpoolId = @SpoolId AND W.WeldId = ( SELECT TOP 1 WAL.WeldId FROM WeldActivityLog WAL WHERE WAL.WeldId = W.WeldId AND WAL.WeldStatusId <> @WeldStatusId ORDER BY LogDate DESC ) ) RETURN @TotalActiveWeldCount ENDGOgo/* Create procedure/function _Spool_CountNotWeldStatus. */CREATE PROCEDURE _Spool_CountNotWeldStatus ( @SpoolId int, @WeldStatusId int )AS SET NOCOUNT ON DECLARE @TotalWeldCount int BEGIN SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Spool_CountNotWeldStatus (@SpoolId, @WeldStatusId) ) RETURN @TotalWeldCount ENDGOgo/* Create procedure/function FN_Drawing_CountWeldStatus. */CREATE FUNCTION FN_Drawing_CountWeldStatus ( @DrawingId int, @WeldStatusId int )RETURNS intAS BEGIN DECLARE @TotalActiveWeldCount int SET @TotalActiveWeldCount = ( SELECT count(W.WeldId) FROM Weld W WHERE W.DrawingId = @DrawingId AND W.WeldId = ( SELECT TOP 1 WAL.WeldId FROM WeldActivityLog WAL WHERE WAL.WeldId = W.WeldId AND WAL.WeldStatusId = @WeldStatusId ORDER BY LogDate DESC ) ) + ( SELECT count(W.WeldId) FROM Drawing D, Spool S, Weld W WHERE D.DrawingId = S.DrawingId AND D.DrawingId = @DrawingId AND W.WeldId = S.DrawingId AND W.WeldId = ( SELECT TOP 1 WAL.WeldId FROM WeldActivityLog WAL WHERE WAL.WeldId = W.WeldId AND WAL.WeldStatusId = @WeldStatusId ORDER BY LogDate DESC ) ) + ( SELECT SUM(dbo.FN_Drawing_CountWeldStatus(D.DrawingId, @WeldStatusId)) FROM Drawing D WHERE D.ParentDrawingId = @DrawingId ) RETURN @TotalActiveWeldCount END GOgo/* Create procedure/function _Drawing_CountWeldStatus. */CREATE PROCEDURE _Drawing_CountWeldStatus ( @DrawingId int, @WeldStatusId int )AS SET NOCOUNT ON DECLARE @TotalWeldCount int BEGIN SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Drawing_CountWeldStatus(@DrawingId, @WeldStatusId) ) RETURN @TotalWeldCount ENDGOgo/* Create procedure/function FN_Drawing_CountNotWeldStatus. */CREATE FUNCTION FN_Drawing_CountNotWeldStatus ( @DrawingId int, @WeldStatusId int )RETURNS intAS BEGIN DECLARE @TotalActiveWeldCount int SET @TotalActiveWeldCount = ( SELECT count(W.WeldId) FROM Weld W WHERE W.DrawingId = @DrawingId AND W.WeldId = ( SELECT TOP 1 WAL.WeldId FROM WeldActivityLog WAL WHERE WAL.WeldId = W.WeldId AND WAL.WeldStatusId <> @WeldStatusId ORDER BY LogDate DESC ) ) + ( SELECT count(W.WeldId) FROM Drawing D, Spool S, Weld W WHERE D.DrawingId = S.DrawingId AND D.DrawingId = @DrawingId AND W.WeldId = S.DrawingId AND W.WeldId = ( SELECT TOP 1 WAL.WeldId FROM WeldActivityLog WAL WHERE WAL.WeldId = W.WeldId AND WAL.WeldStatusId <> @WeldStatusId ORDER BY LogDate DESC ) ) + ( SELECT SUM(dbo.FN_Drawing_GetInActiveWeldCount(D.DrawingId, @WeldStatusId)) FROM Drawing D WHERE D.ParentDrawingId <> @DrawingId ) RETURN @TotalActiveWeldCount END GOgo/* Create procedure/function _Drawing_CountNotWeldStatus. */CREATE PROCEDURE _Drawing_CountNotWeldStatus ( @DrawingId int, @WeldStatusId int )AS SET NOCOUNT ON DECLARE @TotalWeldCount int BEGIN SET @TotalWeldCount = ( SELECT * FROM dbo.FN_Drawing_CountNotWeldStatus(@DrawingId, @WeldStatusId) ) RETURN @TotalWeldCount ENDGOgo/* Create procedure/function FN_Weld_CountWeldTestResult. */CREATE FUNCTION FN_Weld_CountWeldTestResult ( @WeldId int, @TestResultId int )RETURNS intAS BEGIN DECLARE @TotalTestStatusCount int SET @TotalTestStatusCount = ( SELECT count(WT.WeldId) FROM WeldTest WT WHERE WT.WeldId = @WeldId AND WT.TestResultId = @TestResultId ) RETURN @TotalTestStatusCount ENDGOgo/* Create procedure/function _Weld_CountWeldTestResult. */CREATE PROCEDURE _Weld_CountWeldTestResult ( @WeldId int, @TestResultId int )AS SET NOCOUNT ON DECLARE @TotalTestStatusCount int BEGIN SET @TotalTestStatusCount = ( SELECT * FROM dbo.FN_Weld_CountWeldTestResult(@WeldId, @TestResultId) ) RETURN @TotalTestStatusCount ENDGOgo/* Create procedure/function FN_Weld_CountNotWeldTestResult. */CREATE FUNCTION FN_Weld_CountNotWeldTestResult ( @WeldId int, @TestResultId int )RETURNS intAS BEGIN DECLARE @TotalTestStatusCount int SET @TotalTestStatusCount = ( SELECT count(WT.WeldId) FROM WeldTest WT WHERE WT.WeldId = @WeldId AND WT.TestResultId <> @TestResultId ) RETURN @TotalTestStatusCount ENDGOgo/* Create procedure/function _Weld_CountNotWeldTestResult. */CREATE PROCEDURE _Weld_CountNotWeldTestResult ( @WeldId int, @TestResultId int )AS SET NOCOUNT ON DECLARE @TotalTestStatusCount int BEGIN SET @TotalTestStatusCount = ( SELECT * FROM dbo.FN_Weld_CountWeldNotTestResult(@WeldId, @TestResultId) ) RETURN @TotalTestStatusCount ENDGOgo/* Create procedure/function _Project_CountWeldStatus. */CREATE PROCEDURE _Project_CountWeldStatus(@ProjectId int, @WeldStatusId int )AS SET NOCOUNT ON DECLARE @TotalWeldCount int BEGIN SET @TotalWeldCount = ( SELECT SUM(dbo.FN_Drawing_CountWeldStatus(DrawingId, @WeldStatusId)) FROM Drawing WHERE ProjectId = @ProjectId ) RETURN @TotalWeldCount ENDGOgo/* Create procedure/function _Project_CountNotWeldStatus. */CREATE PROCEDURE _Project_CountNotWeldStatus(@ProjectId int, @WeldStatusId int )AS SET NOCOUNT ON DECLARE @TotalWeldCount int BEGIN SET @TotalWeldCount = ( SELECT SUM(dbo.FN_Drawing_CountNotWeldStatus(DrawingId, @WeldStatusId)) FROM Drawing WHERE ProjectId = @ProjectId ) RETURN @TotalWeldCount ENDGOgo/* Create procedure/function FN_Weld_GetCurrentWeldStatus. */CREATE FUNCTION FN_Weld_GetCurrentWeldStatus ( @WeldId int )RETURNS intAS BEGIN DECLARE @WeldStatusId int SET @WeldStatusId = ( SELECT TOP 1 WeldStatusId FROM WeldActivityLog Where WeldId = @WeldId AND WeldStatusId = 1 ORDER BY LogDate DESC ) RETURN @WeldStatusId ENDGOgo/* Create procedure/function _Weld_GetCurrentWeldStatus. */CREATE PROCEDURE _Weld_GetCurrentWeldStatus(@WeldId int )AS SET NOCOUNT ON DECLARE @WeldStatus int BEGIN SET @WeldStatus = ( SELECT * FROM dbo.FN_Weld_GetCurrentWeldStatus(@WeldId) ) RETURN @WeldStatus ENDGOgo/* Create procedure/function _Welder_InsertNewWelder. */CREATE PROCEDURE _Welder_InsertNewWelder(@EmployeeId int = NULL)AS SET NOCOUNT ON DECLARE @NewWelderId varchar(3) BEGIN IF @EmployeeId IS NULL BEGIN RAISERROR ('_Welder_InsertNewWelder :: EmployeeId must be assigned a value and cannot be null', 16, 1) END ELSE BEGIN EXEC _Welder_GetNextNewWelder @NewWelderId OUTPUT IF @NewWelderId IS NULL BEGIN RAISERROR ('_Welder_InsertNewWelder :: NewWelderId was returned as null from procedure _Welder_InsertNewWelder', 16, 1) END ELSE BEGIN -- Do the Insert All is Good INSERT INTO Welder (WelderId, EmployeeId) VALUES (ltrim(rtrim(@NewWelderId)), @EmployeeId) IF @@ERROR <> 0 BEGIN RAISERROR ('_Welder_InsertNewWelder :: There was an issue while inserting new Welder', 16, 1) END -- END IF END -- END IF END -- END IF ENDRETURNGOgo/* Create procedure/function GetSpecMatrixTemplate. */DROP PROCEDURE GetSpecMatrixTemplateGOCREATE PROCEDURE GetSpecMatrixTemplateAS DECLARE @TemplateCode varchar(32), @TestColumnCount int, @DataColumnCount int BEGIN SET @TemplateCode = 'B31.3' -- Check to see the requested code actually exists If Exists( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) BEGIN -- Check to see if there are actually any test columns SET @TestColumnCount = ( SELECT Count(SpecificationTestColumnIdLayout) FROM SpecificationTestColumnLayout STCL WHERE STCL.TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) ) IF @TestColumnCount > 0 BEGIN -- Go Get The Columns CREATE TABLE #TestColumnTable ( Id int not null identity, TestColumnIdLayout int, WeldTestTypeId int ) INSERT INTO #TestColumnTable (TestColumnIdLayout, WeldTestTypeId) SELECT SpecificationTestColumnIdLayout, WeldTestTypeId FROM SpecificationTestColumnLayout WHERE TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) END -- END IF -- Check to see if there are actually any data columns SET @DataColumnCount = ( SELECT Count(SpecificationMatrixDataColumnIdLayout) FROM SpecificationMatrixDataColumnLayout STCL WHERE STCL.TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) ) IF @DataColumnCount > 0 BEGIN -- Go Get The Columns CREATE TABLE #DataColumnTable ( Id int not null identity, DataColumnIdLayout int, ColumnName varchar(50) ) INSERT INTO #DataColumnTable (DataColumnIdLayout, ColumnName) SELECT SpecificationMatrixDataColumnIdLayout, NameLayout FROM SpecificationMatrixDataColumnLayout WHERE TemplateIdLayout = ( SELECT SMTL.TemplateIdLayout FROM SpecificationMatrixTemplateLayout SMTL WHERE SMTL.SpecificationCodeLayout = @TemplateCode ) END -- END IF IF @DataColumnCount > 0 OR @TestColumnCount > 0 BEGIN CREATE TABLE #MasterColumnHeadings ( ColumnId int not null identity, ColumnIdLayout int not null, ColumnName varchar(50), ColumnType char(4) ) INSERT INTO #MasterColumnHeadings (ColumnIdLayout, ColumnName, ColumnType) SELECT DataColumnIdLayout, ColumnName, 'DATA' FROM #DataColumnTable INSERT INTO #MasterColumnHeadings (ColumnIdLayout, ColumnName, ColumnType) SELECT TCT.TestColumnIdLayout, WTT.Abbreviation, 'TEST' FROM #TestColumnTable TCT, WeldTestType WTT WHERE TCT.WeldTestTypeId = WTT.WeldTestTypeId SELECT * FROM #MasterColumnHeadings END -- END IF END -- END IF ENDGOgo/* Create/Recreate user defined triggers for all the newly create and changed tables. *//* Create table level triggers for table Spool. */CREATE TRIGGER Spool_RevisionStarter ON SpoolFOR INSERT AS DECLARE @SpoolId int BEGIN SET @SpoolId = ( SELECT SpoolId FROM Inserted ) EXEC _SpoolRevision_InsertNewRevision @SpoolId ENDGOgo/* Create table level triggers for table Drawing. */CREATE TRIGGER Drawing_RevisionStarter ON DrawingFOR INSERT AS DECLARE @DrawingId int BEGIN SET @DrawingId = ( SELECT DrawingId FROM Inserted ) EXEC _DrawingRevision_InsertNewRevision @DrawingId ENDGOgo/* Create table level triggers for table Employee. */CREATE TRIGGER Welder_WelderIdStarter ON EmployeeFOR INSERT AS DECLARE @EmployeeId int BEGIN SET @EmployeeId = ( SELECT EmployeeId FROM Inserted ) EXEC _Welder_InsertNewWelder @EmployeeId ENDGOgo