ICS Schema

ics_schema.sql

CREATE TABLE [dbo].[Issues]
(
  [seq_num] [int]  NOT NULL,
  [create date] [smalldatetime]  NULL,
  [customer] [nvarchar](50)  NULL,
  [issue_name] [varchar](MAX)  NULL,
  [submitter_name] [nvarchar](75)  NULL,
  [email_address] [varchar](MAX)  NULL,
  [category] [nvarchar](64)  NULL,
  [priority] [int]  NULL,
  [showstop] [bit]  NULL,
  [exception] [bit]  NULL,
  [exceptionnum] [nvarchar](50)  NULL,
  [FB_Key] [nvarchar](255)  NULL,
  [issue_description] [ntext]  NULL,
  [person_responsible] [nvarchar](32)  NULL,
  [reassigned] [nvarchar](50)  NULL,
  [status] [nvarchar](32)  NULL,
  [resolution_description] [ntext]  NULL,
  [resolved_by] [nvarchar](32)  NULL,
  [resolved_dtm] [smalldatetime]  NULL,
  [test_requirements] [ntext]  NULL,
  [Est_Time_Research] [nvarchar](50)  NULL,
  [Est_Time_Develop] [nvarchar](50)  NULL,
  [Est_Time_Test] [nvarchar](50)  NULL,
  [implement_date] [nvarchar](50)  NULL,
  [dat_tab_chng] [ntext]  NULL,
  [rv_queries] [ntext]  NULL,
  [desc_flow] [ntext]  NULL,
  [%T04] [nvarchar](50)  NULL,
  [Monitor Internally] [int]  NULL,
  [timestamp] [smalldatetime]  NULL,
  [Internally Responsible] [varchar](50)  NULL,
  [Source] [int]  NULL,
  [DueDate] [smalldatetime]  NULL,
  [CommitmentDate] [smalldatetime]  NULL,
  [SCAC] [varchar](50)  NULL,
  [Trax Region] [int]  NULL
)
ALTER TABLE [dbo].[Issues] ADD CONSTRAINT PK_Issues PRIMARY KEY  ([seq_num])

SET ANSI_NULLS ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure [dbo].[CreateNewICS]
	@Customer			nvarchar(32),
	@HandOffDate		SmallDateTime,
	@Issue_Name			ntext,
	@Submitter_Name		nvarchar(75),
	@Person_Responsible nvarchar(32),
	@Email_Address		nvarchar(max),
	@Category			nvarchar(32),
	@Priority			int,
	@ShowStop			bit,
	@Exception			bit,
	@ExceptionNum		nvarchar(50),
	@FB_Key				nvarchar(255), 
	@Issue_Description	ntext,
	@Monitor			bit,
	@Source				int = null,
	@DueDate			SmallDateTime = Null,
	@CommitmentDate		SmallDateTime = Null
--	@@Sequence_Number int OUTPUT
as
SET NOCOUNT ON

/*
2014.03.16 MarkG Card 51379 Add the [email protected] 
to the cc: for ICS communications for the type = 'Query/Report'

2016.05.10 Jerry Pang JiraHD170 - undo change Card51379 (above)
*/


Begin Transaction
Declare @Sequence_Number int;
declare @defaultTraxteam varchar(200)
declare @defaultRegion int
declare @Email_Address_Final nvarchar(max)

--Default region and trax team added by Marco Chacon
Select @defaultTraxteam = [_Internally Responsible].Name, @defaultRegion = [Default Trax Region] 
From [_Common Categories Config]
Left join [_Internally Responsible] on [_Internally Responsible].[identity] = [Default internally responsible]
Where [category name] = @Category and [Disabled] <> 1

if ( @defaultRegion is null and @defaultTraxteam is null) 
begin
Select @defaultTraxteam = [_Internally Responsible].Name, @defaultRegion = [Default Trax Region] 
From [_Customer Categories Config]
Left join [_Internally Responsible] on [_Internally Responsible].[identity] = [Default internally responsible]
Where [category name] = @Category and customer = @Customer
end

Select @Sequence_Number = IsNull((Select Max([Issues].[Seq_Num]) + 1 As Sequence_Number From [Issues]), 1)

select @Person_Responsible = cg.Customer 
from [_Customer Groups] cg 
inner join [_Customers Config] cc on cc.[display ordinal] = cg.[Identity]
where cc.customer = @Customer and ([disabled] is null or [disabled] <> 1)


Select @Email_Address_Final = 
	--2016.05.10 Jerry Pang JiraHD170 - undo change Card51379 (above)
	--(case when @Category like 'query%report' and @Email_Address not like '%[email protected]%'
	--		then '[email protected];' 
	--		else '' 
	-- end) 
	--+ 
	isnull(@Email_Address,'')


Insert Into dbo.Issues (
	    	[seq_num], [timestamp], [customer], [issue_name], 
			[submitter_name], [person_responsible], [email_address], 
			[category], [priority], [showstop], [exception], [exceptionnum], 
			[FB_Key], [issue_description], [status], [Monitor Internally],[CommitmentDate], 
			[Create Date], [Source], [DueDate],[Internally responsible],[Trax Region]) 
	VALUES (
			@Sequence_Number,
			@HandOffDate,
			@Customer,
			@Issue_name,
			@Submitter_name,
			@Person_Responsible,
			@Email_Address_Final,
			@Category,
			@Priority,
			@ShowStop,
			@Exception,
			@ExceptionNum,
			@FB_Key,
			@Issue_Description,
			'NEW',
			@Monitor,
			@CommitmentDate,
			GetDate(),
			@Source,
			@DueDate,
			@defaultTraxteam,
			@defaultRegion);

Commit

Return @Sequence_Number
GO


CREATE TABLE [dbo].[Uploads]
(
  [FileId] [int] IDENTITY(1,1) NOT NULL,
  [Seq_Num] [int]  NOT NULL,
  [Uploaded] [smalldatetime]  NOT NULL,
  [FileSize] [int]  NOT NULL,
  [GUID] [char](38)  NOT NULL,
  [UploadedBy] [varchar](32)  NOT NULL,
  [FileName] [varchar](255)  NOT NULL,
  [ContentType] [varchar](80)  NOT NULL,
  [Description] [varchar](255)  NULL
)
ALTER TABLE [dbo].[Uploads] ADD CONSTRAINT PK_Uploads PRIMARY KEY  ([FileId])

SET ANSI_NULLS OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object:  Stored Procedure dbo.InsertAttachment    Script Date: 3/18/2005 7:36:30 AM ******/
CREATE PROCEDURE [dbo].[InsertAttachment] 
	@fileName varchar(255),
	@description varchar(255),
	@seqnum int,
	@fileSize int,
	@contentType varchar(80),
	@guid char(38),
             @uploadedBy varchar(32),
             @uploaded smalldatetime
AS


Insert 
	Into Uploads 
		([FileName], [Description], Seq_Num, FileSize, ContentType, GUID, UploadedBy, Uploaded) 

Values 
	(@fileName, @description, @seqnum, @fileSize, @contentType, @Guid, @uploadedBy, @uploaded);
GO