· 4 years ago · Mar 29, 2021, 09:00 AM
1/*
2Script created by SQL Examiner 7.1.8.293 at 29-03-2021 09:52:41.
3Run this script on BRUNO-SQL.PRITRI to make it the same as [192.168.222.254,1433].PRITRI
4*/
5USE [PRITRI]
6GO
7SET NOCOUNT ON
8SET NOEXEC OFF
9SET ARITHABORT ON
10SET XACT_ABORT ON
11SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
12GO
13
14BEGIN TRAN
15GO
16
17--step 1: dbo.TDU_GuiasAssistencia: drop default DF_GuiasAssistencia_DataInicio---------------------
18IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_GuiasAssistencia_DataInicio]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
19ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [DF_GuiasAssistencia_DataInicio]
20GO
21IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 1 is completed with errors' ROLLBACK TRAN END
22GO
23IF @@TRANCOUNT = 0 BEGIN PRINT 'step 1 is completed with errors' SET NOEXEC ON END
24GO
25
26--step 2: dbo.TDU_GuiasAssistencia: drop default DF_GuiasAssistencia_Data---------------------------
27IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_GuiasAssistencia_Data]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
28ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [DF_GuiasAssistencia_Data]
29GO
30IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 2 is completed with errors' ROLLBACK TRAN END
31GO
32IF @@TRANCOUNT = 0 BEGIN PRINT 'step 2 is completed with errors' SET NOEXEC ON END
33GO
34
35--step 3: dbo.TDU_GuiasAssistencia: drop default DF_GuiasAssistencia_NHoras-------------------------
36IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_GuiasAssistencia_NHoras]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
37ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [DF_GuiasAssistencia_NHoras]
38GO
39IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 3 is completed with errors' ROLLBACK TRAN END
40GO
41IF @@TRANCOUNT = 0 BEGIN PRINT 'step 3 is completed with errors' SET NOEXEC ON END
42GO
43
44--step 4: dbo.TDU_GuiasAssistencia: drop default DF_GuiasAssistencia_Estado-------------------------
45IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_GuiasAssistencia_Estado]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
46ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [DF_GuiasAssistencia_Estado]
47GO
48IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 4 is completed with errors' ROLLBACK TRAN END
49GO
50IF @@TRANCOUNT = 0 BEGIN PRINT 'step 4 is completed with errors' SET NOEXEC ON END
51GO
52
53--step 5: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_TipoInterv_DF-------------
54IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_TipoInterv_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
55ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_TipoInterv_DF]
56GO
57IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 5 is completed with errors' ROLLBACK TRAN END
58GO
59IF @@TRANCOUNT = 0 BEGIN PRINT 'step 5 is completed with errors' SET NOEXEC ON END
60GO
61
62--step 6: dbo.TDU_GuiasAssistencia: drop default DF_TDU_GuiasAssistencia_CDU_Intervalo--------------
63IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_TDU_GuiasAssistencia_CDU_Intervalo]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
64ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [DF_TDU_GuiasAssistencia_CDU_Intervalo]
65GO
66IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 6 is completed with errors' ROLLBACK TRAN END
67GO
68IF @@TRANCOUNT = 0 BEGIN PRINT 'step 6 is completed with errors' SET NOEXEC ON END
69GO
70
71--step 7: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_Factor_DF-----------------
72IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_Factor_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
73ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_Factor_DF]
74GO
75IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 7 is completed with errors' ROLLBACK TRAN END
76GO
77IF @@TRANCOUNT = 0 BEGIN PRINT 'step 7 is completed with errors' SET NOEXEC ON END
78GO
79
80--step 8: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_Revisao_DF----------------
81IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_Revisao_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
82ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_Revisao_DF]
83GO
84IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 8 is completed with errors' ROLLBACK TRAN END
85GO
86IF @@TRANCOUNT = 0 BEGIN PRINT 'step 8 is completed with errors' SET NOEXEC ON END
87GO
88
89--step 9: dbo.TDU_GuiasAssistencia: drop default DF_TDU_GuiasAssistencia_CDU_DataCriacao------------
90IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_TDU_GuiasAssistencia_CDU_DataCriacao]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
91ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [DF_TDU_GuiasAssistencia_CDU_DataCriacao]
92GO
93IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 9 is completed with errors' ROLLBACK TRAN END
94GO
95IF @@TRANCOUNT = 0 BEGIN PRINT 'step 9 is completed with errors' SET NOEXEC ON END
96GO
97
98--step 10: dbo.TDU_GuiasAssistencia: drop default DF_TDU_GuiasAssistencia_CDU_ChaveLoginAutomatico--
99IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF_TDU_GuiasAssistencia_CDU_ChaveLoginAutomatico]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
100ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [DF_TDU_GuiasAssistencia_CDU_ChaveLoginAutomatico]
101GO
102IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 10 is completed with errors' ROLLBACK TRAN END
103GO
104IF @@TRANCOUNT = 0 BEGIN PRINT 'step 10 is completed with errors' SET NOEXEC ON END
105GO
106
107--step 11: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_IdPedido_DF--------------
108IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_IdPedido_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
109ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_IdPedido_DF]
110GO
111IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 11 is completed with errors' ROLLBACK TRAN END
112GO
113IF @@TRANCOUNT = 0 BEGIN PRINT 'step 11 is completed with errors' SET NOEXEC ON END
114GO
115
116--step 12: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_IdMarcacao_DF------------
117IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_IdMarcacao_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
118ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_IdMarcacao_DF]
119GO
120IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 12 is completed with errors' ROLLBACK TRAN END
121GO
122IF @@TRANCOUNT = 0 BEGIN PRINT 'step 12 is completed with errors' SET NOEXEC ON END
123GO
124
125--step 13: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_RecomendarTecnico_DF-----
126IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_RecomendarTecnico_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
127ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_RecomendarTecnico_DF]
128GO
129IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 13 is completed with errors' ROLLBACK TRAN END
130GO
131IF @@TRANCOUNT = 0 BEGIN PRINT 'step 13 is completed with errors' SET NOEXEC ON END
132GO
133
134--step 14: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_CtrIncluido_DF-----------
135IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_CtrIncluido_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
136ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_CtrIncluido_DF]
137GO
138IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 14 is completed with errors' ROLLBACK TRAN END
139GO
140IF @@TRANCOUNT = 0 BEGIN PRINT 'step 14 is completed with errors' SET NOEXEC ON END
141GO
142
143--step 15: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_CTRLivres_DF-------------
144IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_CTRLivres_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
145ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_CTRLivres_DF]
146GO
147IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 15 is completed with errors' ROLLBACK TRAN END
148GO
149IF @@TRANCOUNT = 0 BEGIN PRINT 'step 15 is completed with errors' SET NOEXEC ON END
150GO
151
152--step 16: dbo.TDU_GuiasAssistencia: drop default TDU_GuiasAssistencia_CDU_CtrAnalise_DF------------
153IF EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia_CDU_CtrAnalise_DF]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
154ALTER TABLE [dbo].[TDU_GuiasAssistencia] DROP CONSTRAINT [TDU_GuiasAssistencia_CDU_CtrAnalise_DF]
155GO
156IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 16 is completed with errors' ROLLBACK TRAN END
157GO
158IF @@TRANCOUNT = 0 BEGIN PRINT 'step 16 is completed with errors' SET NOEXEC ON END
159GO
160
161--step 17: create temp table tmp_TDU_GuiasAssistencia-----------------------------------------------
162CREATE TABLE [dbo].[tmp_TDU_GuiasAssistencia] (
163 [CDU_Numero] [bigint] IDENTITY(1, 1) NOT NULL,
164 [CDU_DataInicio] [datetime] NOT NULL CONSTRAINT [DF_GuiasAssistencia_DataInicio] DEFAULT (getdate()),
165 [CDU_DataFim] [datetime] NOT NULL CONSTRAINT [DF_GuiasAssistencia_Data] DEFAULT (getdate()),
166 [CDU_Cliente] [nvarchar](12) COLLATE Latin1_General_CI_AS NOT NULL,
167 [CDU_Processo] [int] NULL,
168 [CDU_Problema] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
169 [CDU_Condicoes] [nvarchar](2) COLLATE Latin1_General_CI_AS NULL,
170 [CDU_Servico] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
171 [CDU_NHoras] [float] NULL CONSTRAINT [DF_GuiasAssistencia_NHoras] DEFAULT ((0.25)),
172 [CDU_Distancia] [nvarchar](12) COLLATE Latin1_General_CI_AS NULL,
173 [CDU_Estado] [smallint] NOT NULL CONSTRAINT [DF_GuiasAssistencia_Estado] DEFAULT ((0)),
174 [CDU_Tecnico] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
175 [CDU_TipoProblema] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
176 [CDU_TipoInterv] [int] NOT NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_TipoInterv_DF] DEFAULT ((0)),
177 [CDU_NHorasIntervalo] [float] NULL CONSTRAINT [DF_TDU_GuiasAssistencia_CDU_Intervalo] DEFAULT ((0)),
178 [CDU_Factura_Encomenda] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
179 [CDU_Guia_Origem] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
180 [CDU_Factor] [float] NOT NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_Factor_DF] DEFAULT ((1)),
181 [CDU_ProcessoATP] [int] NULL,
182 [CDU_IntervencaoATP] [int] NULL,
183 [CDU_DataIntegracao] [datetime] NULL,
184 [CDU_UtilizadorIntegracao] [nvarchar](25) COLLATE Latin1_General_CI_AS NULL,
185 [CDU_Revisao] [bit] NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_Revisao_DF] DEFAULT ((0)),
186 [CDU_NaoAceite] [bit] NULL,
187 [CDU_EsclarecimentoPedido] [bit] NULL,
188 [CDU_EnviadaPara] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
189 [CDU_DataCriacao] [datetime] NULL CONSTRAINT [DF_TDU_GuiasAssistencia_CDU_DataCriacao] DEFAULT (getdate()),
190 [CDU_EventoId] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
191 [CDU_NHorasDeslocacao] [float] NULL,
192 [CDU_Chave] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_TDU_GuiasAssistencia_CDU_ChaveLoginAutomatico] DEFAULT (newid()),
193 [CDU_EnviarPara] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
194 [CDU_ComentarioCliente] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
195 [CDU_NumContrato] [int] NULL,
196 [CDU_TipoDocContrato] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
197 [CDU_SerieContrato] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
198 [CDU_PontuacaoServico] [int] NULL,
199 [CDU_PontuacaoTecnico] [int] NULL,
200 [CDU_PontuacaoTempo] [int] NULL,
201 [CDU_PontuacaoGlobal] [int] NULL,
202 [CDU_DataQuestionario] [datetime] NULL,
203 [CDU_EmailQuestionario] [nvarchar](256) COLLATE Latin1_General_CI_AS NULL,
204 [CDU_EventoIdProximo] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
205 [CDU_TipoDoc] [nvarchar](5) COLLATE Latin1_General_CI_AS NULL,
206 [CDU_NumEncomenda] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,
207 [CDU_SerieEncomenda] [nvarchar](6) COLLATE Latin1_General_CI_AS NULL,
208 [CDU_Responsavel] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
209 [CDU_IdPedido] [int] NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_IdPedido_DF] DEFAULT ((0)),
210 [CDU_IdMarcacao] [bigint] NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_IdMarcacao_DF] DEFAULT ((0)),
211 [CDU_RecomendarTecnico] [bit] NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_RecomendarTecnico_DF] DEFAULT ((0)),
212 [CDU_Associada] [bit] NULL,
213 [CDU_GuiaPai] [bigint] NULL,
214 [CDU_DuracaoCalculada] [int] NULL,
215 [CDU_DistanciaCalculada] [int] NULL,
216 [CDU_CondicoesOriginal] [nvarchar](2) COLLATE Latin1_General_CI_AS NULL,
217 [CDU_CtrIncluido] [float] NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_CtrIncluido_DF] DEFAULT ((0)),
218 [CDU_CTRLivres] [float] NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_CTRLivres_DF] DEFAULT ((0)),
219 [CDU_CtrAnalise] [float] NULL CONSTRAINT [TDU_GuiasAssistencia_CDU_CtrAnalise_DF] DEFAULT ((0)),
220 [CDU_CTR_Tipo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL,
221 [CDU_ObservacoesInternas] [ntext] COLLATE Latin1_General_CI_AS NULL,
222 [CDU_VersaoTSM] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
223 [CDU_SubCondicao] [int] NULL,
224 [CDU_ContratoCProjeto] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
225 [CDU_Revisto] [bit] NULL,
226 [CDU_DataRevisao] [datetime] NULL,
227 [CDU_UtilizadorRevisao] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
228 [CDU_DataInicioOriginalRevisto] [datetime] NULL,
229 [CDU_DuracaoOriginalRevisto] [float] NULL,
230 [CDU_IntervaloOriginalRevisto] [float] NULL,
231 [CDU_DataFimOriginalRevisto] [datetime] NULL,
232 [CDU_CondicoesOriginalRevisto] [nvarchar](2) COLLATE Latin1_General_CI_AS NULL,
233 [CDU_SubCondicaoOriginalRevisto] [int] NULL,
234 [CDU_ContratoCProjetoOriginalRevisto] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
235 [CDU_TipoDocOriginalRevisto] [nvarchar](5) COLLATE Latin1_General_CI_AS NULL,
236 [CDU_NumEncomendaOriginalRevisto] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,
237 [CDU_SerieEncomendaOriginalRevisto] [nvarchar](6) COLLATE Latin1_General_CI_AS NULL,
238 [CDU_ResponsavelOriginalRevisto] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
239 [CDU_UtilizadorUltimaAlteracao] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
240 [CDU_DataUltimaAlteracao] [datetime] NULL
241) ON [PRIMARY]
242GO
243IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 17 is completed with errors' ROLLBACK TRAN END
244GO
245IF @@TRANCOUNT = 0 BEGIN PRINT 'step 17 is completed with errors' SET NOEXEC ON END
246GO
247
248--step 18: SET IDENTITY_INSERT [dbo].[tmp_TDU_GuiasAssistencia] ON----------------------------------
249SET IDENTITY_INSERT [dbo].[tmp_TDU_GuiasAssistencia] ON
250GO
251IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 18 is completed with errors' ROLLBACK TRAN END
252GO
253IF @@TRANCOUNT = 0 BEGIN PRINT 'step 18 is completed with errors' SET NOEXEC ON END
254GO
255
256--step 19: copy existing data into new table tmp_TDU_GuiasAssistencia-------------------------------
257INSERT INTO [dbo].[tmp_TDU_GuiasAssistencia]([CDU_Numero], [CDU_DataInicio], [CDU_DataFim], [CDU_Cliente], [CDU_Processo], [CDU_Problema], [CDU_Condicoes], [CDU_Servico], [CDU_NHoras], [CDU_Distancia], [CDU_Estado], [CDU_Tecnico], [CDU_TipoProblema], [CDU_TipoInterv], [CDU_NHorasIntervalo], [CDU_Factura_Encomenda], [CDU_Guia_Origem], [CDU_Factor], [CDU_ProcessoATP], [CDU_IntervencaoATP], [CDU_DataIntegracao], [CDU_UtilizadorIntegracao], [CDU_Revisao], [CDU_NaoAceite], [CDU_EsclarecimentoPedido], [CDU_EnviadaPara], [CDU_DataCriacao], [CDU_EventoId], [CDU_NHorasDeslocacao], [CDU_Chave], [CDU_EnviarPara], [CDU_ComentarioCliente], [CDU_NumContrato], [CDU_TipoDocContrato], [CDU_SerieContrato], [CDU_PontuacaoServico], [CDU_PontuacaoTecnico], [CDU_PontuacaoTempo], [CDU_PontuacaoGlobal], [CDU_DataQuestionario], [CDU_EmailQuestionario], [CDU_EventoIdProximo], [CDU_TipoDoc], [CDU_NumEncomenda], [CDU_SerieEncomenda], [CDU_Responsavel], [CDU_IdPedido], [CDU_IdMarcacao], [CDU_RecomendarTecnico], [CDU_Associada], [CDU_GuiaPai], [CDU_DuracaoCalculada], [CDU_DistanciaCalculada], [CDU_CondicoesOriginal], [CDU_CtrIncluido], [CDU_CTRLivres], [CDU_CtrAnalise], [CDU_CTR_Tipo], [CDU_ObservacoesInternas], [CDU_VersaoTSM], [CDU_SubCondicao], [CDU_ContratoCProjeto], [CDU_Revisto], [CDU_DataRevisao], [CDU_UtilizadorRevisao], [CDU_DataInicioOriginalRevisto], [CDU_DuracaoOriginalRevisto], [CDU_IntervaloOriginalRevisto], [CDU_DataFimOriginalRevisto], [CDU_CondicoesOriginalRevisto], [CDU_SubCondicaoOriginalRevisto], [CDU_ContratoCProjetoOriginalRevisto], [CDU_TipoDocOriginalRevisto], [CDU_NumEncomendaOriginalRevisto], [CDU_SerieEncomendaOriginalRevisto], [CDU_ResponsavelOriginalRevisto], [CDU_UtilizadorUltimaAlteracao], [CDU_DataUltimaAlteracao]) SELECT
258 [CDU_Numero],
259 [CDU_DataInicio],
260 [CDU_DataFim],
261 [CDU_Cliente],
262 [CDU_Processo],
263 [CDU_Problema],
264 [CDU_Condicoes],
265 [CDU_Servico],
266 [CDU_NHoras],
267 [CDU_Distancia],
268 [CDU_Estado],
269 [CDU_Tecnico],
270 [CDU_TipoProblema],
271 [CDU_TipoInterv],
272 [CDU_NHorasIntervalo],
273 [CDU_Factura_Encomenda],
274 [CDU_Guia_Origem],
275 [CDU_Factor],
276 [CDU_ProcessoATP],
277 [CDU_IntervencaoATP],
278 [CDU_DataIntegracao],
279 [CDU_UtilizadorIntegracao],
280 [CDU_Revisao],
281 [CDU_NaoAceite],
282 [CDU_EsclarecimentoPedido],
283 [CDU_EnviadaPara],
284 [CDU_DataCriacao],
285 [CDU_EventoId],
286 [CDU_NHorasDeslocacao],
287 [CDU_Chave],
288 [CDU_EnviarPara],
289 [CDU_ComentarioCliente],
290 NULL,
291 NULL,
292 NULL,
293 [CDU_PontuacaoServico],
294 [CDU_PontuacaoTecnico],
295 [CDU_PontuacaoTempo],
296 [CDU_PontuacaoGlobal],
297 [CDU_DataQuestionario],
298 [CDU_EmailQuestionario],
299 [CDU_EventoIdProximo],
300 [CDU_TipoDoc],
301 [CDU_NumEncomenda],
302 [CDU_SerieEncomenda],
303 [CDU_Responsavel],
304 [CDU_IdPedido],
305 [CDU_IdMarcacao],
306 [CDU_RecomendarTecnico],
307 [CDU_Associada],
308 [CDU_GuiaPai],
309 [CDU_DuracaoCalculada],
310 [CDU_DistanciaCalculada],
311 [CDU_CondicoesOriginal],
312 [CDU_CtrIncluido],
313 [CDU_CTRLivres],
314 [CDU_CtrAnalise],
315 [CDU_CTR_Tipo],
316 [CDU_ObservacoesInternas],
317 [CDU_VersaoTSM],
318 [CDU_SubCondicao],
319 NULL,
320 [CDU_Revisto],
321 [CDU_DataRevisao],
322 [CDU_UtilizadorRevisao],
323 [CDU_DataInicioOriginalRevisto],
324 [CDU_DuracaoOriginalRevisto],
325 [CDU_IntervaloOriginalRevisto],
326 [CDU_DataFimOriginalRevisto],
327 [CDU_CondicoesOriginalRevisto],
328 [CDU_SubCondicaoOriginalRevisto],
329 [CDU_ContratoCProjetoOriginalRevisto],
330 NULL,
331 NULL,
332 NULL,
333 NULL,
334 [CDU_UtilizadorUltimaAlteracao],
335 [CDU_DataUltimaAlteracao]
336FROM [dbo].[TDU_GuiasAssistencia]
337GO
338IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 19 is completed with errors' ROLLBACK TRAN END
339GO
340IF @@TRANCOUNT = 0 BEGIN PRINT 'step 19 is completed with errors' SET NOEXEC ON END
341GO
342
343--step 20: SET IDENTITY_INSERT [dbo].[tmp_TDU_GuiasAssistencia] OFF---------------------------------
344SET IDENTITY_INSERT [dbo].[tmp_TDU_GuiasAssistencia] OFF
345GO
346IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 20 is completed with errors' ROLLBACK TRAN END
347GO
348IF @@TRANCOUNT = 0 BEGIN PRINT 'step 20 is completed with errors' SET NOEXEC ON END
349GO
350
351--step 21: dbo.TDU_LinhaArtigoGuia: drop foreign key FK__TDU_Linha__CDU_N__63FF5688-----------------
352IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__TDU_Linha__CDU_N__63FF5688]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_LinhaArtigoGuia]'))
353ALTER TABLE [dbo].[TDU_LinhaArtigoGuia] DROP CONSTRAINT [FK__TDU_Linha__CDU_N__63FF5688]
354GO
355IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 21 is completed with errors' ROLLBACK TRAN END
356GO
357IF @@TRANCOUNT = 0 BEGIN PRINT 'step 21 is completed with errors' SET NOEXEC ON END
358GO
359
360--step 22: dbo.TDU_LinhaTecnicoGuia: drop foreign key FK__TDU_Linha__CDU_N__66DBC333----------------
361IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__TDU_Linha__CDU_N__66DBC333]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_LinhaTecnicoGuia]'))
362ALTER TABLE [dbo].[TDU_LinhaTecnicoGuia] DROP CONSTRAINT [FK__TDU_Linha__CDU_N__66DBC333]
363GO
364IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 22 is completed with errors' ROLLBACK TRAN END
365GO
366IF @@TRANCOUNT = 0 BEGIN PRINT 'step 22 is completed with errors' SET NOEXEC ON END
367GO
368
369--step 23: dbo.TDU_LinhaDimensionamentoGuia: drop foreign key FK_TDU_LinhaDimensionamentoGuia_TDU_GuiasAssistencia
370IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_LinhaDimensionamentoGuia_TDU_GuiasAssistencia]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_LinhaDimensionamentoGuia]'))
371ALTER TABLE [dbo].[TDU_LinhaDimensionamentoGuia] DROP CONSTRAINT [FK_TDU_LinhaDimensionamentoGuia_TDU_GuiasAssistencia]
372GO
373IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 23 is completed with errors' ROLLBACK TRAN END
374GO
375IF @@TRANCOUNT = 0 BEGIN PRINT 'step 23 is completed with errors' SET NOEXEC ON END
376GO
377
378--step 24: drop table dbo.TDU_GuiasAssistencia------------------------------------------------------
379IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND type in (N'U'))
380DROP TABLE [dbo].[TDU_GuiasAssistencia]
381GO
382IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 24 is completed with errors' ROLLBACK TRAN END
383GO
384IF @@TRANCOUNT = 0 BEGIN PRINT 'step 24 is completed with errors' SET NOEXEC ON END
385GO
386
387--step 25: rename tmp_TDU_GuiasAssistencia to dbo.TDU_GuiasAssistencia------------------------------
388DECLARE @i int
389EXEC @i = sp_rename N'[dbo].[tmp_TDU_GuiasAssistencia]', N'TDU_GuiasAssistencia'
390IF @i <> 0 BEGIN PRINT 'step 25 is completed with errors' ROLLBACK TRAN END
391GO
392IF @@TRANCOUNT = 0 BEGIN PRINT 'step 25 is completed with errors' SET NOEXEC ON END
393GO
394
395--step 26: dbo.TDU_GuiasAssistencia: add primary key PK_GuiasAssistencia_1--------------------------
396IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'PK_GuiasAssistencia_1')
397ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [PK_GuiasAssistencia_1] PRIMARY KEY CLUSTERED ([CDU_Numero])
398GO
399IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 26 is completed with errors' ROLLBACK TRAN END
400GO
401IF @@TRANCOUNT = 0 BEGIN PRINT 'step 26 is completed with errors' SET NOEXEC ON END
402GO
403
404--step 27: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K12_2_3_7_9_27_41 to table dbo.TDU_GuiasAssistencia
405IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_2_3_7_9_27_41')
406CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_2_3_7_9_27_41] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico]) INCLUDE([CDU_DataInicio], [CDU_DataFim], [CDU_Condicoes], [CDU_NHoras], [CDU_DataCriacao], [CDU_NumEncomenda]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
407GO
408IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 27 is completed with errors' ROLLBACK TRAN END
409GO
410IF @@TRANCOUNT = 0 BEGIN PRINT 'step 27 is completed with errors' SET NOEXEC ON END
411GO
412
413--step 28: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K13_K2_9_11_36 to table dbo.TDU_GuiasAssistencia
414IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K13_K2_9_11_36')
415CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K13_K2_9_11_36] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico], [CDU_TipoProblema], [CDU_DataInicio]) INCLUDE([CDU_NHoras], [CDU_Estado], [CDU_PontuacaoGlobal]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
416GO
417IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 28 is completed with errors' ROLLBACK TRAN END
418GO
419IF @@TRANCOUNT = 0 BEGIN PRINT 'step 28 is completed with errors' SET NOEXEC ON END
420GO
421
422--step 29: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K2_27_33_34_35_36_37_46 to table dbo.TDU_GuiasAssistencia
423IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K2_27_33_34_35_36_37_46')
424CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K2_27_33_34_35_36_37_46] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico], [CDU_DataInicio]) INCLUDE([CDU_PontuacaoTempo], [CDU_PontuacaoGlobal], [CDU_DataQuestionario], [CDU_RecomendarTecnico], [CDU_DataCriacao], [CDU_PontuacaoServico], [CDU_PontuacaoTecnico]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
425GO
426IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 29 is completed with errors' ROLLBACK TRAN END
427GO
428IF @@TRANCOUNT = 0 BEGIN PRINT 'step 29 is completed with errors' SET NOEXEC ON END
429GO
430
431--step 30: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K27_K3_K1_2_9_46 to table dbo.TDU_GuiasAssistencia
432IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K27_K3_K1_2_9_46')
433CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K27_K3_K1_2_9_46] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico], [CDU_DataCriacao], [CDU_DataFim], [CDU_Numero]) INCLUDE([CDU_DataInicio], [CDU_NHoras], [CDU_RecomendarTecnico]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
434GO
435IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 30 is completed with errors' ROLLBACK TRAN END
436GO
437IF @@TRANCOUNT = 0 BEGIN PRINT 'step 30 is completed with errors' SET NOEXEC ON END
438GO
439
440--step 31: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K27_K4_K1_2_3_5_6_7_8_9_10_11_13_14_15_16_17_18_19_20_21_22_23_24_25_26_28_ to table dbo.TDU_GuiasAssistencia
441IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K27_K4_K1_2_3_5_6_7_8_9_10_11_13_14_15_16_17_18_19_20_21_22_23_24_25_26_28_')
442CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K12_K27_K4_K1_2_3_5_6_7_8_9_10_11_13_14_15_16_17_18_19_20_21_22_23_24_25_26_28_] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico], [CDU_DataCriacao], [CDU_Cliente], [CDU_Numero]) INCLUDE([CDU_DataInicio], [CDU_DataFim], [CDU_Processo], [CDU_Problema], [CDU_Condicoes], [CDU_Servico], [CDU_NHoras], [CDU_Distancia], [CDU_Estado], [CDU_TipoProblema], [CDU_TipoInterv], [CDU_NHorasIntervalo], [CDU_Factura_Encomenda], [CDU_Guia_Origem], [CDU_Factor], [CDU_ProcessoATP], [CDU_IntervencaoATP], [CDU_DataIntegracao], [CDU_UtilizadorIntegracao], [CDU_Revisao], [CDU_NaoAceite], [CDU_EsclarecimentoPedido], [CDU_EnviadaPara], [CDU_EventoId], [CDU_NHorasDeslocacao], [CDU_Chave], [CDU_EnviarPara], [CDU_ComentarioCliente], [CDU_PontuacaoServico], [CDU_PontuacaoTecnico], [CDU_PontuacaoTempo], [CDU_PontuacaoGlobal], [CDU_DataQuestionario], [CDU_EmailQuestionario], [CDU_EventoIdProximo], [CDU_TipoDoc], [CDU_NumEncomenda], [CDU_SerieEncomenda], [CDU_Responsavel], [CDU_IdPedido], [CDU_IdMarcacao], [CDU_RecomendarTecnico], [CDU_Associada], [CDU_GuiaPai], [CDU_DuracaoCalculada], [CDU_DistanciaCalculada], [CDU_CondicoesOriginal], [CDU_CtrIncluido], [CDU_CTRLivres], [CDU_CtrAnalise], [CDU_CTR_Tipo], [CDU_VersaoTSM], [CDU_SubCondicao], [CDU_NumContrato], [CDU_TipoDocContrato], [CDU_SerieContrato]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
443GO
444IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 31 is completed with errors' ROLLBACK TRAN END
445GO
446IF @@TRANCOUNT = 0 BEGIN PRINT 'step 31 is completed with errors' SET NOEXEC ON END
447GO
448
449--step 32: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K21_K4_K12_K7_K14_K40_K42_K41_K2_1_6_8_9 to table dbo.TDU_GuiasAssistencia
450IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K21_K4_K12_K7_K14_K40_K42_K41_K2_1_6_8_9')
451CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K21_K4_K12_K7_K14_K40_K42_K41_K2_1_6_8_9] ON [dbo].[TDU_GuiasAssistencia]([CDU_DataIntegracao], [CDU_Cliente], [CDU_Tecnico], [CDU_Condicoes], [CDU_TipoInterv], [CDU_TipoDoc], [CDU_SerieEncomenda], [CDU_NumEncomenda], [CDU_DataInicio]) INCLUDE([CDU_Servico], [CDU_NHoras], [CDU_Numero], [CDU_Problema]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
452GO
453IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 32 is completed with errors' ROLLBACK TRAN END
454GO
455IF @@TRANCOUNT = 0 BEGIN PRINT 'step 32 is completed with errors' SET NOEXEC ON END
456GO
457
458--step 33: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K42_K40_K41_4_9_21 to table dbo.TDU_GuiasAssistencia
459IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K42_K40_K41_4_9_21')
460CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K42_K40_K41_4_9_21] ON [dbo].[TDU_GuiasAssistencia]([CDU_SerieEncomenda], [CDU_TipoDoc], [CDU_NumEncomenda]) INCLUDE([CDU_Cliente], [CDU_NHoras], [CDU_DataIntegracao]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
461GO
462IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 33 is completed with errors' ROLLBACK TRAN END
463GO
464IF @@TRANCOUNT = 0 BEGIN PRINT 'step 33 is completed with errors' SET NOEXEC ON END
465GO
466
467--step 34: add index _dta_index_TDU_GuiasAssistencia_14_1975312630__K42_K40_K41_9 to table dbo.TDU_GuiasAssistencia
468IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_14_1975312630__K42_K40_K41_9')
469CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_14_1975312630__K42_K40_K41_9] ON [dbo].[TDU_GuiasAssistencia]([CDU_SerieEncomenda], [CDU_TipoDoc], [CDU_NumEncomenda]) INCLUDE([CDU_NHoras]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
470GO
471IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 34 is completed with errors' ROLLBACK TRAN END
472GO
473IF @@TRANCOUNT = 0 BEGIN PRINT 'step 34 is completed with errors' SET NOEXEC ON END
474GO
475
476--step 35: add index _dta_index_TDU_GuiasAssistencia_21_79000454__K12_K3_K1_2_33_34_35_36_37_38 to table dbo.TDU_GuiasAssistencia
477IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_21_79000454__K12_K3_K1_2_33_34_35_36_37_38')
478CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_21_79000454__K12_K3_K1_2_33_34_35_36_37_38] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico], [CDU_DataFim], [CDU_Numero]) INCLUDE([CDU_DataInicio], [CDU_PontuacaoServico], [CDU_PontuacaoTecnico], [CDU_PontuacaoTempo], [CDU_PontuacaoGlobal], [CDU_DataQuestionario], [CDU_EmailQuestionario]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
479GO
480IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 35 is completed with errors' ROLLBACK TRAN END
481GO
482IF @@TRANCOUNT = 0 BEGIN PRINT 'step 35 is completed with errors' SET NOEXEC ON END
483GO
484
485--step 36: add index _dta_index_TDU_GuiasAssistencia_21_79000454__K12_K3_K1_36 to table dbo.TDU_GuiasAssistencia
486IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'_dta_index_TDU_GuiasAssistencia_21_79000454__K12_K3_K1_36')
487CREATE NONCLUSTERED INDEX [_dta_index_TDU_GuiasAssistencia_21_79000454__K12_K3_K1_36] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico], [CDU_DataFim], [CDU_Numero]) INCLUDE([CDU_PontuacaoGlobal]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
488GO
489IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 36 is completed with errors' ROLLBACK TRAN END
490GO
491IF @@TRANCOUNT = 0 BEGIN PRINT 'step 36 is completed with errors' SET NOEXEC ON END
492GO
493
494--step 37: add index NonClusteredIndex-20150108-185419 to table dbo.TDU_GuiasAssistencia------------
495IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'NonClusteredIndex-20150108-185419')
496CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150108-185419] ON [dbo].[TDU_GuiasAssistencia]([CDU_Cliente]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
497GO
498IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 37 is completed with errors' ROLLBACK TRAN END
499GO
500IF @@TRANCOUNT = 0 BEGIN PRINT 'step 37 is completed with errors' SET NOEXEC ON END
501GO
502
503--step 38: add index NonClusteredIndex-20150108-185437 to table dbo.TDU_GuiasAssistencia------------
504IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'NonClusteredIndex-20150108-185437')
505CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150108-185437] ON [dbo].[TDU_GuiasAssistencia]([CDU_Tecnico]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
506GO
507IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 38 is completed with errors' ROLLBACK TRAN END
508GO
509IF @@TRANCOUNT = 0 BEGIN PRINT 'step 38 is completed with errors' SET NOEXEC ON END
510GO
511
512--step 39: add index NonClusteredIndex-20150108-185450 to table dbo.TDU_GuiasAssistencia------------
513IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'NonClusteredIndex-20150108-185450')
514CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150108-185450] ON [dbo].[TDU_GuiasAssistencia]([CDU_TipoInterv]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
515GO
516IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 39 is completed with errors' ROLLBACK TRAN END
517GO
518IF @@TRANCOUNT = 0 BEGIN PRINT 'step 39 is completed with errors' SET NOEXEC ON END
519GO
520
521--step 40: add index NonClusteredIndex-20150108-185505 to table dbo.TDU_GuiasAssistencia------------
522IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'NonClusteredIndex-20150108-185505')
523CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150108-185505] ON [dbo].[TDU_GuiasAssistencia]([CDU_Condicoes]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
524GO
525IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 40 is completed with errors' ROLLBACK TRAN END
526GO
527IF @@TRANCOUNT = 0 BEGIN PRINT 'step 40 is completed with errors' SET NOEXEC ON END
528GO
529
530--step 41: add index NonClusteredIndex-20150108-185544 to table dbo.TDU_GuiasAssistencia------------
531IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]') AND name = N'NonClusteredIndex-20150108-185544')
532CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150108-185544] ON [dbo].[TDU_GuiasAssistencia]([CDU_DataInicio]) WITH ( PAD_INDEX = ON, FILLFACTOR = 80 ) ON [PRIMARY]
533GO
534IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 41 is completed with errors' ROLLBACK TRAN END
535GO
536IF @@TRANCOUNT = 0 BEGIN PRINT 'step 41 is completed with errors' SET NOEXEC ON END
537GO
538
539--step 42: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_VersoesTSM_GuiasAssistencia-------------
540IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_VersoesTSM_GuiasAssistencia]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
541ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_VersoesTSM_GuiasAssistencia] FOREIGN KEY ([CDU_VersaoTSM]) REFERENCES [dbo].[TDU_VersaoTSM] ([CDU_Versao])
542GO
543IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 42 is completed with errors' ROLLBACK TRAN END
544GO
545IF @@TRANCOUNT = 0 BEGIN PRINT 'step 42 is completed with errors' SET NOEXEC ON END
546GO
547
548--step 43: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_GuiasAssistencia_TiposProblemas---------
549IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_GuiasAssistencia_TiposProblemas]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
550ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_GuiasAssistencia_TiposProblemas] FOREIGN KEY ([CDU_TipoProblema]) REFERENCES [dbo].[TiposProblemas] ([TipoProblema])
551GO
552IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 43 is completed with errors' ROLLBACK TRAN END
553GO
554IF @@TRANCOUNT = 0 BEGIN PRINT 'step 43 is completed with errors' SET NOEXEC ON END
555GO
556
557--step 44: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_GuiasAssistencia_Tecnicos---------------
558IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_GuiasAssistencia_Tecnicos]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
559ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_GuiasAssistencia_Tecnicos] FOREIGN KEY ([CDU_Tecnico]) REFERENCES [dbo].[Tecnicos] ([Tecnico])
560GO
561IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 44 is completed with errors' ROLLBACK TRAN END
562GO
563IF @@TRANCOUNT = 0 BEGIN PRINT 'step 44 is completed with errors' SET NOEXEC ON END
564GO
565
566--step 45: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_GuiasAssistencia_TDU_TipoInterv---------
567IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_GuiasAssistencia_TDU_TipoInterv]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
568ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_GuiasAssistencia_TDU_TipoInterv] FOREIGN KEY ([CDU_TipoInterv]) REFERENCES [dbo].[TDU_TipoInterv] ([CDU_Codigo])
569GO
570IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 45 is completed with errors' ROLLBACK TRAN END
571GO
572IF @@TRANCOUNT = 0 BEGIN PRINT 'step 45 is completed with errors' SET NOEXEC ON END
573GO
574
575--step 46: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_GuiasAssistencia_TDU_GuiasAssistencia---
576IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_GuiasAssistencia_TDU_GuiasAssistencia]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
577ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_GuiasAssistencia_TDU_GuiasAssistencia] FOREIGN KEY ([CDU_Numero]) REFERENCES [dbo].[TDU_GuiasAssistencia] ([CDU_Numero])
578GO
579IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 46 is completed with errors' ROLLBACK TRAN END
580GO
581IF @@TRANCOUNT = 0 BEGIN PRINT 'step 46 is completed with errors' SET NOEXEC ON END
582GO
583
584--step 47: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_GuiasAssistencia_TDU_CondicoesInterv1---
585IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_GuiasAssistencia_TDU_CondicoesInterv1]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
586ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_GuiasAssistencia_TDU_CondicoesInterv1] FOREIGN KEY ([CDU_CondicoesOriginal]) REFERENCES [dbo].[TDU_CondicoesInterv] ([CDU_Codigo])
587GO
588IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 47 is completed with errors' ROLLBACK TRAN END
589GO
590IF @@TRANCOUNT = 0 BEGIN PRINT 'step 47 is completed with errors' SET NOEXEC ON END
591GO
592
593--step 48: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_GuiasAssistencia_TDU_CondicoesInterv----
594IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_GuiasAssistencia_TDU_CondicoesInterv]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
595ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_GuiasAssistencia_TDU_CondicoesInterv] FOREIGN KEY ([CDU_Condicoes]) REFERENCES [dbo].[TDU_CondicoesInterv] ([CDU_Codigo])
596GO
597IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 48 is completed with errors' ROLLBACK TRAN END
598GO
599IF @@TRANCOUNT = 0 BEGIN PRINT 'step 48 is completed with errors' SET NOEXEC ON END
600GO
601
602--step 49: dbo.TDU_GuiasAssistencia: add foreign key FK_TDU_GuiasAssistencia_Clientes---------------
603IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_GuiasAssistencia_Clientes]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_GuiasAssistencia]'))
604ALTER TABLE [dbo].[TDU_GuiasAssistencia] ADD CONSTRAINT [FK_TDU_GuiasAssistencia_Clientes] FOREIGN KEY ([CDU_Cliente]) REFERENCES [dbo].[Clientes] ([Cliente])
605GO
606IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 49 is completed with errors' ROLLBACK TRAN END
607GO
608IF @@TRANCOUNT = 0 BEGIN PRINT 'step 49 is completed with errors' SET NOEXEC ON END
609GO
610
611--step 50: dbo.TDU_LinhaDimensionamentoGuia: add foreign key FK_TDU_LinhaDimensionamentoGuia_TDU_GuiasAssistencia
612IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TDU_LinhaDimensionamentoGuia_TDU_GuiasAssistencia]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_LinhaDimensionamentoGuia]'))
613ALTER TABLE [dbo].[TDU_LinhaDimensionamentoGuia] ADD CONSTRAINT [FK_TDU_LinhaDimensionamentoGuia_TDU_GuiasAssistencia] FOREIGN KEY ([CDU_Guia]) REFERENCES [dbo].[TDU_GuiasAssistencia] ([CDU_Numero])
614GO
615IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 50 is completed with errors' ROLLBACK TRAN END
616GO
617IF @@TRANCOUNT = 0 BEGIN PRINT 'step 50 is completed with errors' SET NOEXEC ON END
618GO
619
620--step 51: dbo.TDU_LinhaTecnicoGuia: add foreign key FK__TDU_Linha__CDU_N__66DBC333-----------------
621IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__TDU_Linha__CDU_N__66DBC333]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_LinhaTecnicoGuia]'))
622ALTER TABLE [dbo].[TDU_LinhaTecnicoGuia] ADD CONSTRAINT [FK__TDU_Linha__CDU_N__66DBC333] FOREIGN KEY ([CDU_NumGuia]) REFERENCES [dbo].[TDU_GuiasAssistencia] ([CDU_Numero])
623GO
624IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 51 is completed with errors' ROLLBACK TRAN END
625GO
626IF @@TRANCOUNT = 0 BEGIN PRINT 'step 51 is completed with errors' SET NOEXEC ON END
627GO
628
629--step 52: dbo.TDU_LinhaArtigoGuia: add foreign key FK__TDU_Linha__CDU_N__63FF5688------------------
630IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__TDU_Linha__CDU_N__63FF5688]') AND parent_object_id = OBJECT_ID(N'[dbo].[TDU_LinhaArtigoGuia]'))
631ALTER TABLE [dbo].[TDU_LinhaArtigoGuia] ADD CONSTRAINT [FK__TDU_Linha__CDU_N__63FF5688] FOREIGN KEY ([CDU_NumGuia]) REFERENCES [dbo].[TDU_GuiasAssistencia] ([CDU_Numero])
632GO
633IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 52 is completed with errors' ROLLBACK TRAN END
634GO
635IF @@TRANCOUNT = 0 BEGIN PRINT 'step 52 is completed with errors' SET NOEXEC ON END
636GO
637
638--step 53: Create trigger dbo.IATP_TDU_GuiasAssistencia_GuardaValoresOriginais on table dbo.TDU_GuiasAssistencia
639GO
640SET QUOTED_IDENTIFIER ON
641GO
642SET ANSI_NULLS ON
643GO
644IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[IATP_TDU_GuiasAssistencia_GuardaValoresOriginais]'))
645EXEC sp_executesql N'CREATE TRIGGER [dbo].[IATP_TDU_GuiasAssistencia_GuardaValoresOriginais] ON [dbo].[TDU_GuiasAssistencia]
646 FOR INSERT, UPDATE AS
647BEGIN
648
649DECLARE @Numero bigint
650DECLARE @DataIntegracao datetime
651DECLARE @TipoProblema nvarchar(15)
652DECLARE @IdPedido int
653DECLARE @IdMarcacao int
654
655DECLARE XCURSOR CURSOR LOCAL FOR
656SELECT I.CDU_Numero, I.CDU_DataIntegracao, I.CDU_TipoProblema, CDU_IdPedido, CDU_IdMarcacao
657FROM INSERTED I
658
659OPEN XCURSOR
660FETCH NEXT FROM XCURSOR INTO @Numero, @DataIntegracao, @TipoProblema, @IdPedido, @IdMarcacao
661WHILE (@@FETCH_STATUS = 0)
662BEGIN
663
664 --Atualiza o Tipo de Problema da Marcação
665 update m set m.CDU_TipoProblema=@TipoProblema from TDU_Marcacoes m where m.CDU_Id=@IdMarcacao
666
667 --Atualiza o Tipo de Problema do Pedido
668 update p set p.CDU_TipoProblema=@TipoProblema from TDU_PedidosAssistencia p where p.CDU_IdPedido=@IdPedido
669
670 FETCH NEXT FROM XCURSOR INTO @Numero, @DataIntegracao, @TipoProblema, @IdPedido, @IdMarcacao
671END
672
673CLOSE XCURSOR
674DEALLOCATE XCURSOR
675
676END;'
677GO
678IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 53 is completed with errors' ROLLBACK TRAN END
679GO
680IF @@TRANCOUNT = 0 BEGIN PRINT 'step 53 is completed with errors' SET NOEXEC ON END
681GO
682
683----------------------------------------------------------------------
684IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN PRINT 'Synchronization is successfully completed.' END
685GO
686SET NOEXEC OFF
687GO
688
689