USE [HelpDesk02_Def]
GO

/*** DELETE VAM ASSET FIELDS ***/
DELETE from tblDtsFields WHERE nDataViewID=3;

DELETE tblDtsFields WHERE nID IN (780,781,782,783,784,785, 786, 787);

/*** RENAME OLD TASK FIELDS: REMOVE THEM FROM VIEWS, QUERIES, ETC***/
UPDATE tblDtsFields
	SET [tCaption]='OLD - ' + tCaption
	WHERE [tTableName]='tblTask' AND [nID]>9999	

GO

/*** TASKS ***/
IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=781)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType],
		[nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], 
		[fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], 
		[tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
		[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], 
		[tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
		VALUES (781, N'tblTask', 32, N'Tasks___Due_Date', 1, 8, 50, N'Task - Due Date', 
		0, 0, NULL, NULL, 1, 1, 1, NULL, 1, 1, NULL, N'Due Date', NULL, N'781', 20, NULL, 1, 2, 1, NULL, NULL, NULL, 3, NULL);

		
IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=782)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType],
		[nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], 
		[fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], 
		[tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
		[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], 
		[tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
		VALUES (782, N'tblTask', 32, N'Tasks___Support_Team', 1, 103, 3, N'Task - Support Team', 
		0, 0, N'WorkTeam', 16, 1, 1, 1, NULL, 1, 1, NULL, N'Support Team', NULL, N'782', 20, NULL, 1, 2, 1, NULL, NULL, NULL, 4, NULL);

		
IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=783)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType],
		[nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], 
		[fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], 
		[tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
		[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], 
		[tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
		VALUES (783, N'tblTask', 32, N'Tasks___Task_Completion_Date', 1, 8, 50, N'Task - Completion Date', 
		0, 0, NULL, NULL, 1, 1, 1, NULL, 1, 1, NULL, N'Completion Date', NULL, N'783', 20, NULL, 1, 2, 1, NULL, NULL, NULL, 3, NULL);

		 
IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=784)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType],
		[nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], 
		[fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], 
		[tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
		[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], 
		[tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
		VALUES (784, N'tblTask', 32, N'Tasks___Dependency', 1, 103, 50, N'Task - Dependency', 1, 0, N'tblTaskDependency', 
		64, 1, 1, 1, NULL, 1, 1, NULL, N'Dependency', NULL, N'784', 20, NULL, 1, 2, 1, NULL, NULL, NULL, 4, NULL);
		 
		
IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=785)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType],
		[nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], 
		[fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], 
		[tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
		[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], 
		[tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
		VALUES (785, N'tblTask', 32, N'Tasks___Task_Completion_Notes', 1, 12, 50, N'Task - Completion Notes', 
		0, 0, NULL, NULL, 1, 1, 1, NULL, 1, 1, NULL, N'Completion Notes', NULL, N'785', 20, NULL, 1, 2, 1, NULL, NULL, NULL, 3, NULL);

IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=786)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType],
		[nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], 
		[fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], 
		[tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
		[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], 
		[tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
		VALUES (786, N'tblTask', 32, N'Tasks___Task_Status', 1, 103, 50, N'Task - Status', 0, 0, N'tblTaskStatus', 64, 1, 1, 1, NULL, 1, 1, NULL, 
		N'Status', NULL, N'786', 20, NULL, 1, 2, 1, NULL, NULL, NULL, 1, NULL);
		

IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=787)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType],
		[nDataSize], [tCaption], [fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], 
		[fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], [fEditable], [fVisible], [nRelatedField], 
		[tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
		[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], 
		[tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
		VALUES (787, N'tblTask', 32, N'nCompositeID', 1, 102, 0, N'Task - ID', 0, 0, NULL, 0, 1, 0, 1, NULL, 1, 1, 0, 
		N'Task ID', NULL, N'787', 20, NULL, 0, 2, NULL, NULL, NULL, NULL, 1, N'Task - ID');

ELSE
	UPDATE [tblDtsFields]
		SET fCanQueryBy=1,
		[fVisible]=1
		WHERE nID=787;


		
		
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=788)
	UPDATE [tblDtsFields]
		SET tOrigCaption='Tasks',
		[nControlType]=14
		WHERE nID=788;

IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=789)
	UPDATE [tblDtsFields]
		SET tCaption='Task - Name',
		[tLabelCaption]='Name'
		WHERE nID=789;
		
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=790)
	UPDATE [tblDtsFields]
	SET [tCaption]='Tasks - Description',
		[tLabelCaption]='Description'
		WHERE nID=790;

-- Tasks___Assignee
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=791)
	UPDATE [tblDtsFields]
	SET [tCaption]='Tasks - Assignee',
		[tWhere]='Where (([nID] >= 0 And  [tName]  <> '''') Or [tName]  = ''<User>'') And ( Security_Type = 1 OR Security_Type = 8)'
		WHERE nID=791;
		
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=792)
	UPDATE [tblDtsFields]
	SET [tCaption]='Tasks - Start Date',
		[tLabelCaption]='Start Date'
		WHERE nID=792;
		
/*** Repairs ***/		
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=550)
	UPDATE [tblDtsFields]
		SET [tCaption]='Tracking Number',
		[tLabelCaption]='Tracking Number',
		tOrigCaption='Tracking Number',
		fEnabled=1
		WHERE nID=550;

IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=543)
	UPDATE [tblDtsFields]
		SET nDestinationTabID=2
		WHERE nID=543;

IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=544)
	UPDATE [tblDtsFields]
		SET nDestinationTabID=2
		WHERE nID=544;

IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=545)
	UPDATE [tblDtsFields]
		SET nDestinationTabID=2
		WHERE nID=545;

IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=546)
	UPDATE [tblDtsFields]
		SET nDestinationTabID=2
		WHERE nID=546;
		
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=547)
	UPDATE [tblDtsFields]
		SET nDestinationTabID=2
		WHERE nID=547;

IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=548)
	UPDATE [tblDtsFields]
		SET nDestinationTabID=2
		WHERE nID=548;

IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=549)		
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], 
	[fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab],
	[fEditable], [fVisible], [nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
	[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (549, N'tblDts', 32, N'Chargeback_to_Customer', 1, 1, 50, N'Chargeback to Customer', 
	0, 0, NULL, NULL, 1, 1, 1, NULL, 
	1, 1, NULL, N'Chargeback to Customer', NULL, N'549', 2, NULL,
	0, -1, 1, NULL, NULL, NULL, 2, NULL);
		
IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=551)
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], 
	[fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab], 
	[fEditable], [fVisible], [nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
	[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (551, N'tblDts', 32, N'Parts_Repaired', 1, 104, 50, N'Parts Repaired', 
	0, 0, N'tblComputerComponent', 16, 1, 1, 1, NULL, 
	1, 1, NULL, N'Parts Repaired', NULL, N'551', 2, NULL, 
	0, -1, 1, NULL, NULL, NULL, 2, N'Parts Repaired');

IF NOT EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=552)	
	INSERT [dbo].[tblDtsFields] ([nID], [tTableName], [nTableDSType], [tName], [nDataViewID], [nDataType], [nDataSize], [tCaption], 
	[fChoiceOrder], [fRequired], [tFieldValuesTable], [nValueTableDSType], [fEnabled], [fKeepHistory], [fCanQueryBy], [tDestinationTab],
	[fEditable], [fVisible], [nRelatedField], [tLabelCaption], [tAliasSourceTable], [tBoundControlName], [nDestinationTabID], [tWhere], 
	[fIncludeInDefaultReports], [nRelationshipType], [nContainerType], [nControlType], [tValueTableIDFld], [tValueTableNameFld], [nRevisionNumber], [tOrigCaption]) 
	VALUES (552, N'tblDts', 32, N'Cost', 1, 5, 50, N'Cost', 
	0, 0, NULL, NULL, 1, 1, 1, NULL, 
	1, 1, NULL, N'Cost', NULL, N'552', 2, NULL, 
	0, -1, 1, NULL, NULL, NULL, 2, NULL);
	
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=556)
	UPDATE [tblDtsFields]
		SET nDataType=104 ,		
		[tCaption]='Accessories Included',
		[tFieldValuesTable]='tblComputerAccessory',
		[nValueTableDSType]=16,
		[tLabelCaption]='Accessories Included',
		[tBoundControlName]='556',
		[nDestinationTabID]=2, 		
		[nRevisionNumber]=2,
		[tOrigCaption]='Accessories Included'
		WHERE nID=556;

		
IF EXISTS (SELECT * FROM [tblDtsFields] WHERE nID=661)
	UPDATE [tblDtsFields]
		SET [tCaption]='Provider of Service',
		[tLabelCaption]='Provider of Service',
		[tFieldValuesTable]='Supplier',
		nDestinationTabID=2,
		[tWhere]='',
		tOrigCaption='Provider of Service'
		WHERE nID=661;

		
GO
		
		
/*** DELETE RMA Problem Resolution Fields ***/

/*** Work Perfomed By ***/
DELETE [tblDtsFields] WHERE nID=553;

/*** RMA Resolution Resolution Date ***/
DELETE [tblDtsFields] WHERE nID=554;

/*** RMA Problem Resolution Summary ***/
DELETE [tblDtsFields] WHERE nID=731;

/*** RMA Problem Resolution Code ***/
DELETE [tblDtsFields] WHERE nID=732;

/*** RMA Problem Resolution Details Internal ***/
DELETE [tblDtsFields] WHERE nID=733;

/*** Details Log Internal ***/
DELETE [tblDtsFields] WHERE nID=734;

/*** Convert Activity Logs to HTML data type ***/
-- Activity Log
UPDATE [tblDtsFields]
		SET [nDataType]=120
		WHERE nID=56;

-- Activity Log Customer
UPDATE [tblDtsFields]
		SET [nDataType]=120
		WHERE nID=864;

		
GO
		