· 7 years ago · Feb 25, 2019, 12:40 PM
1IF EXISTS( SELECT * FROM SYSOBJECTS WHERE NAME = 'HistoricoComprasRPRequisitante' AND XTYPE = 'TF' )
2 DROP FUNCTION mat.HistoricoComprasRPRequisitante
3GO
4
5CREATE FUNCTION mat.HistoricoComprasRPRequisitante
6(
7 @p_unges CHAR(2)
8 ,@p_nroata INT
9 ,@p_anoata CHAR(4)
10 ,@p_UnidOrcInicial CHAR(15)
11 ,@p_UnidOrcFinal CHAR(15)
12 ,@p_MaterialInicial CHAR(10)
13 ,@p_MaterialFinal CHAR(10)
14 ,@p_DtSolicInicial DATETIME
15 ,@p_DtSolicFinal DATETIME
16)
17
18RETURNS @TMP_SALDO_REQUISITANTE TABLE
19(
20 Ido INT IDENTITY(1,1) PRIMARY KEY
21 ,UngesAta CHAR(2) NOT NULL
22 ,NroAta INT NOT NULL
23 ,AnoAta CHAR(4) NOT NULL
24 ,UngesSC CHAR(2) NULL
25 ,NumSC INT NULL
26 ,AnoSC CHAR(4) NULL
27 ,Nivel1 CHAR(3) NOT NULL
28 ,Nivel2 CHAR(3) NOT NULL
29 ,Nivel3 CHAR(3) NOT NULL
30 ,Nivel4 CHAR(3) NOT NULL
31 ,Nivel5 CHAR(3) NOT NULL
32 ,Estrut CHAR(1) NOT NULL
33 ,Grupo CHAR(2) NOT NULL
34 ,Subgrp CHAR(2) NOT NULL
35 ,Itemat CHAR(4) NOT NULL
36 ,Digmat CHAR(1) NOT NULL
37 ,Qtde_Requisitada DECIMAL(15,3) NULL
38 ,Qtde_SaldoAnt DECIMAL(15,3) NULL
39 ,Qtde_Utilizada DECIMAL(15,3) NULL
40 ,Qtde_Entregue DECIMAL(15,3) NULL
41 ,Qtde_Cancelada DECIMAL(15,3) NULL
42 ,Qtde_Saldo DECIMAL(15,3) NULL
43 ,Qtde_Suprimida DECIMAL(15,3) NULL
44 ,VlrFinanc_Suprimido DECIMAL(15,3) NULL
45 ,Dt_UltSolicit DATETIME NULL
46 ,Vlr_Unitario DECIMAL(15,4) NULL
47 ,Vlr_Total DECIMAL(15,4) NULL
48 ,Saldo_Financeiro DECIMAL(15,4) NULL
49 ,Sigla CHAR(2) NULL
50 ,Convit INT NULL
51 ,Anoc CHAR(4) NULL
52 ,Af INT NULL
53 ,Nafano CHAR(4) NULL
54 ,Tipaf CHAR(1) NULL
55 ,numint INT NULL
56 ,anoint CHAR(4) NULL
57 ,idLote INT NULL
58)
59AS
60BEGIN
61 DECLARE @ano CHAR(4)
62
63 --DECLARE
64 -- @p_unges CHAR(2)
65 -- ,@p_nroata INT
66 -- ,@p_anoata CHAR(4)
67 -- ,@p_UnidOrcInicial CHAR(15)
68 -- ,@p_UnidOrcFinal CHAR(15)
69 -- ,@p_MaterialInicial CHAR(10)
70 -- ,@p_MaterialFinal CHAR(10)
71 -- ,@p_DtSolicInicial DATETIME
72 -- ,@p_DtSolicFinal DATETIME
73
74 --SET @p_unges = '01'
75 --SET @p_nroata = 9
76 --SET @p_anoata = '2012'
77 --SET @p_UnidOrcInicial = NULL
78 --SET @p_UnidOrcFinal = NULL
79 --SET @p_MaterialInicial = '1040300046'
80 --SET @p_MaterialFinal = '1040300046'
81 --SET @p_DtSolicInicial = NULL
82 --SET @p_DtSolicFinal = NULL
83
84IF NOT EXISTS( SELECT 1 FROM MCT90600 c906 WHERE c906.unges = @p_unges AND c906.nroata = @p_nroata AND c906.anoata = @p_anoata )
85 RETURN ;
86
87DECLARE @numreg DECIMAL(6,0),
88 @anoreg CHAR(4),
89 @IdNivel5 INT,
90 @vnivel1 CHAR(3),
91 @vnivel2 CHAR(3),
92 @vnivel3 CHAR(3),
93 @vnivel4 CHAR(3),
94 @vnivel5 CHAR(3),
95 @vanoreg CHAR(4),
96 @vanoatual CHAR(4),
97 @habilitaIntegracaoCP BIT
98
99SELECT @habilitaIntegracaoCP = mat.RetornaIntegracaoCP()
100
101DECLARE @TMP_SECRETARIAS TABLE(
102 Ido INT IDENTITY(1,1),
103 NivelIni1 CHAR(3)
104 ,NivelIni2 CHAR(3)
105 ,NivelIni3 CHAR(3)
106 ,NivelIni4 CHAR(3)
107 ,NivelIni5 CHAR(3)
108 ,AnoIni CHAR(4)
109 ,NivelFim1 CHAR(3)
110 ,NivelFim2 CHAR(3)
111 ,NivelFim3 CHAR(3)
112 ,NivelFim4 CHAR(3)
113 ,NivelFim5 CHAR(3)
114 ,AnoFim CHAR(4)
115)
116
117DECLARE
118 @exercAtual CHAR(4)
119
120SELECT @exercAtual = MAX(ano) FROM MXT70300
121
122-- FILTRA SOMENTE AS SECRETARIAS DENTRO DO PROCESSO
123INSERT INTO @TMP_SECRETARIAS(NivelIni1, NivelIni2, NivelIni3, NivelIni4, NivelIni5,AnoIni)
124SELECT c900.nivel1, c900.nivel2, c900.nivel3, c900.nivel4, c900.nivel5, c900.anoreg
125 FROM MCT90600 c906
126 INNER JOIN MCT90700 c907 ON c907.unges = c906.unges
127 AND c907.nroata = c906.nroata
128 AND c907.anoata = c906.anoata
129 INNER JOIN MCT91200 c912 ON c912.unges = c906.unges
130 AND c912.sigla = c906.sigla
131 AND c912.convit = c906.convit
132 AND c912.anoc = c906.anoc
133 JOIN MCT91300 c913 with (READUNCOMMITTED)
134 ON c913.unges = c912.unges
135 AND c913.codgrupo = c912.codgrupo
136 AND c913.anogrupo = c912.anogrupo
137 JOIN MCT90000 c900 with (READUNCOMMITTED)
138 ON c900.unges = c913.ungesreq
139 AND c900.numreg = c913.numreq
140 AND c900.anoreg = c913.anoreq
141 JOIN MCT90100 c901 with (READUNCOMMITTED)
142 ON c901.unges = c900.unges
143 AND c901.numreg = c900.numreg
144 AND c901.anoreg = c900.anoreg
145 AND c901.estrut = c913.estrut
146 AND c901.grupo = c913.grupo
147 AND c901.subgrp = c913.subgrp
148 AND c901.itemat = c913.itemat
149 AND c901.digmat = c913.digmat
150 WHERE c906.unges = @p_unges
151 AND c906.anoata = @p_anoata
152 AND c906.nroata = @p_nroata
153 AND c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
154 THEN @p_MaterialInicial
155 ELSE c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat
156 END
157
158 AND c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
159 THEN @p_MaterialFinal
160 ELSE c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat
161 END
162 AND NOT EXISTS( SELECT 1
163 FROM @TMP_SECRETARIAS
164 WHERE NivelIni1 = c900.nivel1
165 AND NivelIni2 = c900.nivel2
166 AND NivelIni3 = c900.nivel3
167 AND NivelIni4 = c900.nivel4
168 AND NivelIni5 = c900.nivel5
169 AND AnoIni = c900.anoreg)
170 GROUP BY c900.nivel1, c900.nivel2, c900.nivel3, c900.nivel4, c900.nivel5, c900.anoreg
171
172-- Requisitantes fora do processo
173INSERT INTO @TMP_SECRETARIAS(NivelIni1, NivelIni2, NivelIni3, NivelIni4, NivelIni5,AnoIni)
174 SELECT c909.nivel1, c909.nivel2, c909.nivel3, c909.nivel4, c909.nivel5, c909.anosc
175 FROM MCT90600 c906 with (READUNCOMMITTED)
176 JOIN MCT90900 c909 with (READUNCOMMITTED) ON c906.unges = c909.ungesAta
177 AND c906.nroata = c909.nroata
178 AND c906.anoata = c909.anoata
179 JOIN MCT91100 c911 with (READUNCOMMITTED) ON c911.unges = c909.unges
180 AND c911.numsc = c909.numsc
181 AND c911.anosc = c909.anosc
182 JOIN MCT90700 c907 with (READUNCOMMITTED) ON c907.unges = c906.unges
183 AND c907.nroata = c906.nroata
184 AND c907.anoata = c906.anoata
185 AND c907.estrut = c911.estrut
186 AND c907.grupo = c911.grupo
187 AND c907.subgrp = c911.subgrp
188 AND c907.itemat = c911.itemat
189 AND c907.digmat = c911.digmat
190 JOIN MCT91200 c912 with (READUNCOMMITTED) ON c912.unges = c906.unges
191 AND c912.sigla = c906.sigla
192 AND c912.convit = c906.convit
193 AND c912.anoc = c906.anoc
194 WHERE c906.unges = @p_unges
195 AND c906.anoata = @p_anoata
196 AND c906.nroata = @p_nroata
197 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
198 THEN @p_MaterialInicial
199 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
200 END
201
202 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
203 THEN @p_MaterialFinal
204 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
205 END
206 AND NOT EXISTS( SELECT 1
207 FROM @TMP_SECRETARIAS
208 WHERE NivelIni1 = c909.nivel1
209 AND NivelIni2 = c909.nivel2
210 AND NivelIni3 = c909.nivel3
211 AND NivelIni4 = c909.nivel4
212 AND NivelIni5 = c909.nivel5
213 AND AnoIni = c909.anosc
214 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
215 )
216 GROUP BY c909.nivel1, c909.nivel2, c909.nivel3, c909.nivel4, c909.nivel5, c909.anosc
217
218-- Aditamento
219INSERT INTO @TMP_SECRETARIAS(NivelIni1, NivelIni2, NivelIni3, NivelIni4, NivelIni5,AnoIni)
220SELECT c919.nivel1, c919.nivel2, c919.nivel3, c919.nivel4, c919.nivel5, YEAR(c919.dtadit)
221 FROM MCT90600 c906 with (READUNCOMMITTED)
222 JOIN MCT90700 c907 with (READUNCOMMITTED) ON c907.unges = c906.unges
223 AND c907.nroata = c906.nroata
224 AND c907.anoata = c906.anoata
225 JOIN MCT91900 c919 with (READUNCOMMITTED) ON c919.unges = c906.unges
226 AND c919.nroata = c906.nroata
227 AND c919.anoata = c906.anoata
228 AND c919.estrut = c907.estrut
229 AND c919.grupo = c907.grupo
230 AND c919.subgrp = c907.subgrp
231 AND c919.itemat = c907.itemat
232 AND c919.digmat = c907.digmat
233 where c906.unges = @p_unges
234 AND c906.anoata = @p_anoata
235 AND c906.nroata = @p_nroata
236 AND c919.nivel1 IS NOT NULL
237 AND c919.nivel2 IS NOT NULL
238 AND c919.nivel3 IS NOT NULL
239 AND c919.nivel4 IS NOT NULL
240 AND c919.nivel5 IS NOT NULL
241 AND c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
242 THEN @p_MaterialInicial
243 ELSE c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat
244 END
245
246 AND c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
247 THEN @p_MaterialFinal
248 ELSE c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat
249 END
250 AND NOT EXISTS( SELECT 1
251 FROM @TMP_SECRETARIAS
252 WHERE NivelIni1 = c919.nivel1
253 AND NivelIni2 = c919.nivel2
254 AND NivelIni3 = c919.nivel3
255 AND NivelIni4 = c919.nivel4
256 AND NivelIni5 = c919.nivel5
257 AND AnoIni = YEAR(c919.dtadit))
258 GROUP BY c919.nivel1, c919.nivel2, c919.nivel3, c919.nivel4, c919.nivel5, YEAR(c919.dtadit)
259
260
261DECLARE @NIVEL1 CHAR(3),@NIVEL2 CHAR(3),@NIVEL3 CHAR(3),@NIVEL4 CHAR(3),@NIVEL5 CHAR(3), @ANOREGISTRO CHAR(4)
262DECLARE @NIVEL1_DESTINO CHAR(3),@NIVEL2_DESTINO CHAR(3),@NIVEL3_DESTINO CHAR(3),@NIVEL4_DESTINO CHAR(3),@NIVEL5_DESTINO CHAR(3), @ANOREGISTRO_DESTINO CHAR(4)
263
264DECLARE CURSOR_SECRETARIAS CURSOR FOR
265 SELECT NivelIni1, NivelIni2, NivelIni3, NivelIni4, NivelIni5,AnoIni FROM @TMP_SECRETARIAS
266
267OPEN CURSOR_SECRETARIAS
268
269FETCH NEXT FROM CURSOR_SECRETARIAS INTO @nivel1, @nivel2, @nivel3, @nivel4, @nivel5, @ANOREGISTRO
270
271WHILE @@FETCH_STATUS = 0
272BEGIN
273
274 SET @NIVEL1_DESTINO = NULL
275 SET @NIVEL2_DESTINO = NULL
276 SET @NIVEL3_DESTINO = NULL
277 SET @NIVEL4_DESTINO = NULL
278 SET @NIVEL5_DESTINO = NULL
279 SET @ANOREGISTRO_DESTINO = NULL
280
281 SELECT @NIVEL1_DESTINO = nivel1_destino, @NIVEL2_DESTINO = nivel2_destino, @NIVEL3_DESTINO = nivel3_destino, @NIVEL4_DESTINO = nivel4_destino, @NIVEL5_DESTINO = nivel5_destino, @ANOREGISTRO_DESTINO = exercicio
282 FROM RetornaUnidOrcDestino(@nivel1, @nivel2, @nivel3, @nivel4, @nivel5, @ANOREGISTRO, @exercAtual)
283
284/* UPDATE tmp
285 SET tmp.NivelFim1 = ret.nivel1_destino,
286 tmp.NivelFim2 = ret.nivel2_destino,
287 tmp.NivelFim3 = ret.nivel3_destino,
288 tmp.NivelFim4 = ret.nivel4_destino,
289 tmp.NivelFim5 = ret.nivel5_destino,
290 tmp.AnoFim = ret.exercicio
291 FROM @TMP_SECRETARIAS tmp
292 OUTER APPLY RetornaUnidOrcDestino(@nivel1, @nivel2, @nivel3, @nivel4, @nivel5, @ANOREGISTRO, @exercAtual) ret
293 WHERE tmp.NivelIni1 = @nivel1
294 AND tmp.NivelIni2 = @nivel2
295 AND tmp.NivelIni3 = @nivel3
296 AND tmp.NivelIni4 = @nivel4
297 AND tmp.NivelIni5 = @nivel5
298 AND tmp.AnoIni = @ANOREGISTRO
299*/
300 UPDATE @TMP_SECRETARIAS SET
301 NivelFim1 = @NIVEL1_DESTINO,
302 NivelFim2 = @NIVEL2_DESTINO,
303 NivelFim3 = @NIVEL3_DESTINO,
304 NivelFim4 = @NIVEL4_DESTINO,
305 NivelFim5 = @NIVEL5_DESTINO,
306 AnoFim = @ANOREGISTRO_DESTINO
307 WHERE NivelIni1 = @nivel1
308 AND NivelIni2 = @nivel2
309 AND NivelIni3 = @nivel3
310 AND NivelIni4 = @nivel4
311 AND NivelIni5 = @nivel5
312 AND AnoIni = @ANOREGISTRO
313
314 FETCH NEXT FROM CURSOR_SECRETARIAS INTO @nivel1, @nivel2, @nivel3, @nivel4, @nivel5, @ANOREGISTRO
315END
316
317CLOSE CURSOR_SECRETARIAS
318DEALLOCATE CURSOR_SECRETARIAS
319
320SELECT DISTINCT @ano = MAX(ano) FROM MXT70300
321
322
323---------------------- CRIAR TABELA E INSERIR AS QUANTIDADES REQUISITANTES POR UNIDADE ORÇAMENTÃRIA ----------------------
324DECLARE @TMP_MATERIAIS_REQUISITANTE TABLE (
325 Ido INT IDENTITY(1,1)
326 ,UngesAta CHAR(2) NOT NULL
327 ,Nivel1 CHAR(3) NOT NULL
328 ,Nivel2 CHAR(3) NOT NULL
329 ,Nivel3 CHAR(3) NOT NULL
330 ,Nivel4 CHAR(3) NOT NULL
331 ,Nivel5 CHAR(3) NOT NULL
332 ,Estrut CHAR(1) NOT NULL
333 ,Grupo CHAR(2) NOT NULL
334 ,Subgrp CHAR(2) NOT NULL
335 ,Itemat CHAR(4) NOT NULL
336 ,Digmat CHAR(1) NOT NULL
337 ,Qtde_Requisitada DECIMAL(15,3) NULL
338 ,Qtde_Aditada DECIMAL(15,3) NULL
339 ,Qtde_Inicial DECIMAL(15,3) NULL
340 ,Qtde_Utilizada DECIMAL(15,3) NULL
341 ,Qtde_Entregue DECIMAL(15,3) NULL
342 ,Qtde_Cancelada DECIMAL(15,3) NULL
343 ,Qtde_Suprimida DECIMAL(15,3) NULL
344 ,Qtde_Saldo DECIMAL(15,3) NULL
345 ,VlrFinanc_Suprimido DECIMAL(15, 3) NULL
346 ,Dt_UltSolicit DATETIME NULL
347 ,Vlr_Unitario DECIMAL(15,4) NULL
348 ,Vlr_Total DECIMAL(15,4) NULL
349 ,Saldo_Financeiro DECIMAL(15,4) NULL
350 ,Sigla CHAR(2) NULL
351 ,Convit INT NULL
352 ,Anoc CHAR(4) NULL
353 ,IdLote INT NULL
354)
355
356DECLARE @sigla CHAR(2),
357 @convit INT,
358 @anoc CHAR(4)
359
360
361SELECT @sigla = sigla, @convit = convit, @anoc = anoc
362 FROM MCT90600 c906
363 WHERE c906.nroata = @p_nroata
364 AND c906.anoata = @p_anoata
365 AND c906.unges = @p_unges
366
367
368INSERT INTO @TMP_MATERIAIS_REQUISITANTE(
369 UngesAta, Nivel1, Nivel2, Nivel3, Nivel4, Nivel5,
370 Estrut, Grupo, Subgrp, Itemat, Digmat,
371 Qtde_Requisitada, Qtde_Aditada, Qtde_Inicial, Qtde_Utilizada, Qtde_Entregue, Qtde_Cancelada, Qtde_Suprimida, VlrFinanc_Suprimido, Qtde_Saldo,
372 Dt_UltSolicit, Vlr_Unitario, Vlr_Total, Saldo_Financeiro, Sigla, Convit, Anoc, IdLote)
373SELECT UngesAta = c912.unges
374 ,NivelFim1, NivelFim2, NivelFim3, NivelFim4, NivelFim5
375 ,c901.estrut, c901.grupo, c901.subgrp, c901.itemat, c901.digmat
376 ,Qtde_Requisitada = SUM(c901.quatde)
377 ,Qtde_Aditada = 0
378 ,Qtde_Inicial = 0
379 ,Qtde_Utilizada = 0
380 ,Qtde_Entregue = 0
381 ,Qtde_Cancelada = 0
382 ,Qtde_Suprimida = 0
383 ,VlrFinanc_Suprimido = 0
384 ,Qtde_Saldo = 0
385 ,Dt_UltSolicit = NULL
386 ,Vlr_Unitario = NULL
387 ,Vlr_Total = NULL
388 ,Saldo_Financeiro = NULL
389 ,Sigla = c912.sigla
390 ,Convit = c912.convit
391 ,Anoc = c912.anoc
392 ,IdLote = c913.idLote
393 FROM MCT91200 c912 with (READUNCOMMITTED)
394 JOIN MCT91300 c913 with (READUNCOMMITTED)
395 ON c913.unges = c912.unges
396 AND c913.codgrupo = c912.codgrupo
397 AND c913.anogrupo = c912.anogrupo
398 JOIN MCT90000 c900 with (READUNCOMMITTED)
399 ON c900.unges = c913.ungesreq
400 AND c900.numreg = c913.numreq
401 AND c900.anoreg = c913.anoreq
402 JOIN MCT90100 c901 with (READUNCOMMITTED)
403 ON c901.unges = c900.unges
404 AND c901.numreg = c900.numreg
405 AND c901.anoreg = c900.anoreg
406 AND c901.estrut = c913.estrut
407 AND c901.grupo = c913.grupo
408 AND c901.subgrp = c913.subgrp
409 AND c901.itemat = c913.itemat
410 AND c901.digmat = c913.digmat
411 JOIN @TMP_SECRETARIAS
412 ON NivelIni1 = c900.nivel1
413 AND NivelIni2 = c900.nivel2
414 AND NivelIni3 = c900.nivel3
415 AND NivelIni4 = c900.nivel4
416 AND NivelIni5 = c900.nivel5
417 AND AnoIni = c900.anoreg
418 WHERE c912.unges = @p_unges
419 AND c912.sigla = @sigla
420 AND c912.convit = @convit
421 AND c912.anoc = @anoc
422 AND c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
423 THEN @p_MaterialInicial
424 ELSE c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat
425 END
426
427 AND c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
428 THEN @p_MaterialFinal
429 ELSE c913.estrut + c913.grupo + c913.subgrp + c913.itemat + c913.digmat
430 END
431
432 GROUP BY c912.unges ,NivelFim1, NivelFim2, NivelFim3, NivelFim4, NivelFim5, AnoFim
433 ,c901.estrut, c901.grupo, c901.subgrp, c901.itemat, c901.digmat
434 ,c912.sigla, c912.convit, c912.anoc, c913.idLote
435
436-- INSERIR AS UNIDADES REQUISITANTES FORA DO PROCESSO
437
438INSERT INTO @TMP_MATERIAIS_REQUISITANTE(
439 UngesAta, Nivel1, Nivel2, Nivel3, Nivel4, Nivel5,
440 Estrut, Grupo, Subgrp, Itemat, Digmat,
441 Qtde_Requisitada, Qtde_Aditada, Qtde_Inicial, Qtde_Utilizada, Qtde_Entregue, Qtde_Cancelada, Qtde_Suprimida, VlrFinanc_Suprimido, Qtde_Saldo,
442 Dt_UltSolicit, Vlr_Unitario, Vlr_Total, Saldo_Financeiro, Sigla, Convit, Anoc, IdLote )
443SELECT UngesAta = c906.unges
444 ,NivelFim1, NivelFim2, NivelFim3, NivelFim4, NivelFim5
445 ,c907.estrut, c907.grupo, c907.subgrp, c907.itemat, c907.digmat
446 ,Qtde_Requisitada = 0
447 ,Qtde_Aditada = 0
448 ,Qtde_Inicial = 0
449 ,Qtde_Utilizada = 0
450 ,Qtde_Entregue = 0
451 ,Qtde_Cancelada = 0
452 ,Qtde_Suprimida = 0
453 ,VlrFinanc_Suprimido = 0
454 ,Qtde_Saldo = 0
455 ,Dt_UltSolicit = NULL
456 ,Vlr_Unitario = NULL
457 ,Vlr_Total = NULL
458 ,Saldo_Financeiro = NULL
459 ,Sigla = c906.sigla
460 ,Convit = c906.convit
461 ,Anoc = c906.anoc
462 ,IdLote = c907.idLote
463 FROM MCT90600 c906 with (READUNCOMMITTED)
464 JOIN MCT90700 c907 with (READUNCOMMITTED) ON c907.unges = c906.unges
465 AND c907.nroata = c906.nroata
466 AND c907.anoata = c906.anoata
467 JOIN MCT91900 c919 with (READUNCOMMITTED) ON c919.unges = c907.unges
468 AND c919.nroata = c907.nroata
469 AND c919.anoata = c907.anoata
470 AND c919.estrut = c907.estrut
471 AND c919.grupo = c907.grupo
472 AND c919.subgrp = c907.subgrp
473 AND c919.itemat = c907.itemat
474 AND c919.digmat = c907.digmat
475 AND ISNULL(c919.idLote,0) = ISNULL(c907.idLote,0)
476 JOIN @TMP_SECRETARIAS
477 ON NivelIni1 = c919.nivel1
478 AND NivelIni2 = c919.nivel2
479 AND NivelIni3 = c919.nivel3
480 AND NivelIni4 = c919.nivel4
481 AND NivelIni5 = c919.nivel5
482 AND AnoIni = YEAR(c919.dtadit)
483 WHERE c906.unges = @p_unges
484 AND c906.sigla = @sigla
485 AND c906.convit = @convit
486 AND c906.anoc = @anoc
487 AND c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
488 THEN @p_MaterialInicial
489 ELSE c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat
490 END
491
492 AND c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
493 THEN @p_MaterialFinal
494 ELSE c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat
495 END
496 AND NOT EXISTS( SELECT 1 FROM @TMP_MATERIAIS_REQUISITANTE tmp
497 WHERE tmp.UngesAta = c906.unges
498 AND tmp.Nivel1 = NivelFim1
499 AND tmp.Nivel2 = NivelFim2
500 AND tmp.Nivel3 = NivelFim3
501 AND tmp.Nivel4 = NivelFim4
502 AND tmp.Nivel5 = NivelFim5
503 AND tmp.estrut = c907.estrut
504 AND tmp.grupo = c907.grupo
505 AND tmp.subgrp = c907.subgrp
506 AND tmp.itemat = c907.itemat
507 AND tmp.digmat = c907.digmat
508 AND ISNULL(tmp.IdLote,0) = ISNULL(c907.idLote,0))
509
510 GROUP BY c906.unges ,NivelFim1, NivelFim2, NivelFim3, NivelFim4, NivelFim5, AnoFim
511 ,c907.estrut, c907.grupo, c907.subgrp, c907.itemat, c907.digmat
512 ,c906.sigla, c906.convit, c906.anoc, c907.idLote
513
514
515INSERT INTO @TMP_MATERIAIS_REQUISITANTE(
516 UngesAta, Nivel1, Nivel2, Nivel3, Nivel4, Nivel5,
517 Estrut, Grupo, Subgrp, Itemat, Digmat,
518 Qtde_Requisitada, Qtde_Aditada, Qtde_Inicial, Qtde_Utilizada, Qtde_Entregue, Qtde_Cancelada, Qtde_Suprimida, VlrFinanc_Suprimido, Qtde_Saldo,
519 Dt_UltSolicit, Vlr_Unitario, Vlr_Total, Saldo_Financeiro, Sigla, Convit, Anoc, IdLote)
520SELECT UngesAta = c906.unges
521 ,NivelFim1, NivelFim2, NivelFim3, NivelFim4, NivelFim5
522 ,c907.estrut, c907.grupo, c907.subgrp, c907.itemat, c907.digmat
523 ,Qtde_Requisitada = 0
524 ,Qtde_Aditada = 0
525 ,Qtde_Inicial = 0
526 ,Qtde_Utilizada = 0
527 ,Qtde_Entregue = 0
528 ,Qtde_Cancelada = 0
529 ,Qtde_Suprimida = 0
530 ,VlrFinanc_Suprimido = 0
531 ,Qtde_Saldo = 0
532 ,Dt_UltSolicit = NULL
533 ,Vlr_Unitario = NULL
534 ,Vlr_Total = NULL
535 ,Saldo_Financeiro = NULL
536 ,Sigla = c906.sigla
537 ,Convit = c906.convit
538 ,Anoc = c906.anoc
539 ,IdLote = c907.idLote
540 FROM MCT90600 c906 with (READUNCOMMITTED)
541 JOIN MCT90700 c907 with (READUNCOMMITTED)
542 ON c907.unges = c906.unges
543 AND c907.nroata = c906.nroata
544 AND c907.anoata = c906.anoata
545 JOIN MCT90900 c909 with (READUNCOMMITTED)
546 ON c909.ungesAta = c906.unges
547 AND c909.nroata = c906.nroata
548 AND c909.anoata = c906.anoata
549 JOIN MCT91100 c911 with (READUNCOMMITTED) ON c911.unges = c909.unges
550 AND c911.numsc = c909.numsc
551 AND c911.anosc = c909.anosc
552 AND c911.estrut = c907.estrut
553 AND c911.grupo = c907.grupo
554 AND c911.subgrp = c907.subgrp
555 AND c911.itemat = c907.itemat
556 AND c911.digmat = c907.digmat
557 AND ISNULL(c911.idLote,0) = ISNULL(c907.idLote,0)
558JOIN @TMP_SECRETARIAS ON NivelIni1 = c909.nivel1
559 AND NivelIni2 = c909.nivel2
560 AND NivelIni3 = c909.nivel3
561 AND NivelIni4 = c909.nivel4
562 AND NivelIni5 = c909.nivel5
563 AND AnoIni = c909.anosc
564 WHERE c906.unges = @p_unges
565 AND c906.sigla = @sigla
566 AND c906.convit = @convit
567 AND c906.anoc = @anoc
568 AND c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
569 THEN @p_MaterialInicial
570 ELSE c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat
571 END
572
573 AND c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
574 THEN @p_MaterialFinal
575 ELSE c907.estrut + c907.grupo + c907.subgrp + c907.itemat + c907.digmat
576 END
577 AND NOT EXISTS( SELECT 1 FROM @TMP_MATERIAIS_REQUISITANTE tmp
578 WHERE tmp.UngesAta = c906.unges
579 AND tmp.Nivel1 = NivelFim1
580 AND tmp.Nivel2 = NivelFim2
581 AND tmp.Nivel3 = NivelFim3
582 AND tmp.Nivel4 = NivelFim4
583 AND tmp.Nivel5 = NivelFim5
584 AND tmp.estrut = c907.estrut
585 AND tmp.grupo = c907.grupo
586 AND tmp.subgrp = c907.subgrp
587 AND tmp.itemat = c907.itemat
588 AND tmp.digmat = c907.digmat
589 AND ISNULL(tmp.IdLote,0) = ISNULL(c907.idLote,0))
590 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
591
592 GROUP BY c906.unges ,NivelFim1, NivelFim2, NivelFim3, NivelFim4, NivelFim5,AnoFim
593 ,c907.estrut, c907.grupo, c907.subgrp, c907.itemat, c907.digmat
594 ,c906.sigla, c906.convit, c906.anoc, c907.idLote
595
596---------------------- FINAL ----------------------
597
598-- Com Solicitação de Compras
599INSERT INTO @TMP_SALDO_REQUISITANTE(UngesAta, NroAta, AnoAta, Nivel1, Nivel2, Nivel3, Nivel4, Nivel5
600 ,Estrut, Grupo, Subgrp, Itemat, Digmat, Qtde_Requisitada, Qtde_Utilizada, Qtde_Entregue, Qtde_Cancelada, Qtde_Saldo
601 ,UngesSC, NumSC, AnoSC, Sigla, Convit, Anoc, idLote )
602SELECT UngesAta = c906.unges, NroAta = c906.nroata, AnoAta = c906.anoata
603 ,tmp.NivelFim1
604 ,tmp.NivelFim2
605 ,tmp.NivelFim3
606 ,tmp.NivelFim4
607 ,tmp.NivelFim5
608 ,Estrut = c907.estrut
609 ,Grupo = c907.grupo
610 ,Subgrp = c907.subgrp
611 ,Itemat = c907.itemat
612 ,Digmat = c907.digmat
613-- ,Qtde_Requisitada = SUM(c901.quatde) -- Quantidade requisitada
614 ,Qtde_Requisitada = 0 -- Quantidade requisitada
615 ,Qtde_Utilizada = 0
616 ,Qtde_Entregue = 0
617 ,Qtde_Cancelada = 0
618 ,Qtde_Saldo = 0
619 ,UngesSC = CASE WHEN c911.estrut IS NOT NULL THEN c909.unges ELSE NULL END
620 ,NumSC = CASE WHEN c911.estrut IS NOT NULL THEN c909.numsc ELSE NULL END
621 ,AnoSC = CASE WHEN c911.estrut IS NOT NULL THEN c909.anosc ELSE NULL END
622 ,Sigla = c906.sigla
623 ,Convit = c906.convit
624 ,Anoc = c906.anoc
625 ,idLote = c907.idLote
626 -- Ata
627 FROM MCT90600 c906 WITH(NOLOCK)
628 -- Itens da Ata
629INNER JOIN MCT90700 c907 WITH(NOLOCK) ON c907.unges = c906.unges
630 AND c907.nroata = c906.nroata
631 AND c907.anoata = c906.anoata
632 -- Agrupamento
633INNER JOIN MCT91200 c912 WITH(NOLOCK) ON c912.unges = c906.unges
634 AND c912.sigla = c906.sigla
635 AND c912.convit = c906.convit
636 AND c912.anoc = c906.anoc
637 -- Itens do agrupamento
638INNER JOIN MCT91300 c913 WITH(NOLOCK) ON c913.unges = c912.unges
639 AND c913.codgrupo = c912.codgrupo
640 AND c913.anogrupo = c912.anogrupo
641 AND c913.estrut = c907.estrut
642 AND c913.grupo = c907.grupo
643 AND c913.subgrp = c907.subgrp
644 AND c913.itemat = c907.itemat
645 AND c913.digmat = c907.digmat
646 AND ISNULL(c913.idLote,0) = ISNULL(c907.idLote,0)
647 -- RC / RP
648INNER JOIN MCT90000 c900 WITH(NOLOCK) ON c900.unges = c913.ungesreq
649 AND c900.numreg = c913.numreq
650 AND c900.anoreg = c913.anoreq
651 -- Itens da RC / RP
652INNER JOIN MCT90100 c901 WITH(NOLOCK) ON c901.unges = c900.unges
653 AND c901.numreg = c900.numreg
654 AND c901.anoreg = c900.anoreg
655 AND c901.estrut = c913.estrut
656 AND c901.grupo = c913.grupo
657 AND c901.subgrp = c913.subgrp
658 AND c901.itemat = c913.itemat
659 AND c901.digmat = c913.digmat
660 -- SC
661INNER JOIN MCT90900 c909 WITH(NOLOCK) ON c909.ungesata = c906.unges
662 AND c909.nroata = c906.nroata
663 AND c909.anoata = c906.anoata
664 AND c909.datsol >= CASE WHEN @p_DtSolicInicial IS NOT NULL THEN @p_DtSolicInicial ELSE c909.datsol END
665 AND c909.datsol <= CASE WHEN @p_DtSolicFinal IS NOT NULL THEN @p_DtSolicFinal ELSE c909.datsol END
666 -- Itens da SC
667INNER JOIN MCT91100 c911 WITH(NOLOCK) ON c911.unges = c909.unges
668 AND c911.numsc = c909.numsc
669 AND c911.anosc = c909.anosc
670 AND c911.estrut = c901.estrut
671 AND c911.grupo = c901.grupo
672 AND c911.subgrp = c901.subgrp
673 AND c911.itemat = c901.itemat
674 AND c911.digmat = c901.digmat
675 AND ISNULL(c911.idLote,0) = ISNULL(c907.idLote,0)
676 JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c909.nivel1
677 AND tmp.NivelIni2 = c909.nivel2
678 AND tmp.NivelIni3 = c909.nivel3
679 AND tmp.NivelIni4 = c909.nivel4
680 AND tmp.NivelIni5 = c909.nivel5
681 AND tmp.AnoIni = c909.anosc
682 WHERE c906.unges = CASE WHEN @p_unges IS NOT NULL THEN @p_unges ELSE c906.unges END
683 AND c906.nroata = CASE WHEN @p_nroata IS NOT NULL THEN @p_nroata ELSE c906.nroata END
684 AND c906.anoata = CASE WHEN @p_anoata IS NOT NULL THEN @p_anoata ELSE c906.anoata END
685
686 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 >=
687 CASE WHEN @p_UnidOrcInicial IS NOT NULL THEN @p_UnidOrcInicial
688 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
689
690 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 <=
691 CASE WHEN @p_UnidOrcFinal IS NOT NULL THEN @p_UnidOrcFinal
692 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
693
694 AND c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
695 THEN @p_MaterialInicial
696 ELSE c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat
697 END
698
699 AND c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
700 THEN @p_MaterialFinal
701 ELSE c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat
702 END
703 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
704
705 GROUP BY c906.unges, c906.nroata, c906.anoata,
706 tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5
707 ,c907.estrut, c907.grupo, c907.subgrp, c907.itemat, c907.digmat,c911.estrut
708 ,c909.unges, c911.estrut, c909.numsc, c911.estrut, c909.anosc, c906.sigla, c906.convit, c906.anoc, c907.idLote
709 ORDER BY numsc
710
711 -------------------------------------------------------------------------------------
712 -- Solicitações de compras com requisitantes sem requisição
713 INSERT INTO @TMP_SALDO_REQUISITANTE(UngesAta, NroAta, AnoAta, Nivel1, Nivel2, Nivel3, Nivel4, Nivel5
714 ,Estrut, Grupo, Subgrp, Itemat, Digmat, Qtde_Requisitada, Qtde_Utilizada, Qtde_Entregue, Qtde_Cancelada, Qtde_Saldo
715 ,UngesSC, NumSC, AnoSC, Sigla, Convit, Anoc, idLote)
716 SELECT UngesAta = c906.unges, NroAta = c906.nroata, AnoAta = c906.anoata
717 ,tmp.NivelFim1
718 ,tmp.NivelFim2
719 ,tmp.NivelFim3
720 ,tmp.NivelFim4
721 ,tmp.NivelFim5
722 ,Estrut = c911.estrut
723 ,Grupo = c911.grupo
724 ,Subgrp = c911.subgrp
725 ,Itemat = c911.itemat
726 ,Digmat = c911.digmat
727 ,Qtde_Requisitada = 0
728 ,Qtde_Utilizada = SUM(c911.qtde)
729 ,Qtde_Entregue = 0
730 ,Qtde_Cancelada = 0
731 ,Qtde_Saldo = 0
732 ,UngesSC = c909.unges
733 ,NumSC = c909.numsc
734 ,AnoSC = c909.anosc
735 ,Sigla = c906.sigla
736 ,Convit = c906.convit
737 ,Anoc = c906.anoc
738 ,idLote = c911.idLote
739 -- Solicitação de Compras
740 FROM MCT90900 c909 WITH(NOLOCK)
741 -- Itens da Solicitação de Compras
742 JOIN MCT91100 c911 WITH(NOLOCK) ON c911.unges = c909.unges
743 AND c911.numsc = c909.numsc
744 AND c911.anosc = c909.anosc
745 -- Ata de registro de preços
746 JOIN MCT90600 c906 WITH(NOLOCK) ON c906.unges = c909.ungesAta
747 AND c906.nroata = c909.nroata
748 AND c906.anoata = c909.anoata
749 JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c909.nivel1
750 AND tmp.NivelIni2 = c909.nivel2
751 AND tmp.NivelIni3 = c909.nivel3
752 AND tmp.NivelIni4 = c909.nivel4
753 AND tmp.NivelIni5 = c909.nivel5
754 AND tmp.AnoIni = c909.anosc
755
756
757 WHERE c909.UngesAta = CASE WHEN @p_unges IS NULL THEN c909.UngesAta ELSE @p_unges END
758 AND c909.nroata = CASE WHEN @p_nroata IS NULL THEN c909.nroata ELSE @p_nroata END
759 AND c909.anoata = CASE WHEN @p_anoata IS NULL THEN c909.anoata ELSE @p_anoata END
760 AND c909.datsol >= CASE WHEN @p_DtSolicInicial IS NULL THEN c909.datsol ELSE @p_DtSolicInicial END
761 AND c909.datsol <= CASE WHEN @p_DtSolicFinal IS NULL THEN c909.datsol ELSE @p_DtSolicFinal END
762 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 >=
763 CASE WHEN @p_UnidOrcInicial IS NOT NULL THEN @p_UnidOrcInicial
764 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
765
766 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 <=
767 CASE WHEN @p_UnidOrcFinal IS NOT NULL THEN @p_UnidOrcFinal
768 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
769
770 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
771 THEN @p_MaterialInicial
772 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
773 END
774
775 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
776 THEN @p_MaterialFinal
777 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
778 END
779 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
780
781 AND NOT EXISTS( SELECT 1
782 FROM MCT91200 c912 WITH(NOLOCK)
783 JOIN MCT91300 c913 WITH(NOLOCK) ON c913.unges = c912.unges
784 AND c913.codgrupo = c912.codgrupo
785 AND c913.anogrupo = c912.anogrupo
786 AND c913.estrut = c911.estrut
787 AND c913.grupo = c911.grupo
788 AND c913.subgrp = c911.subgrp
789 AND c913.itemat = c911.itemat
790 AND c913.digmat = c911.digmat
791 AND ISNULL(c913.idLote,0) = ISNULL(c911.idLote,0)
792 JOIN MCT90600 c906 ON c906.unges = c912.unges
793 AND c906.sigla = c912.sigla
794 AND c906.convit = c912.convit
795 AND c906.anoc = c912.anoc
796 AND c906.unges = c909.ungesata
797 JOIN MCT90100 c901 WITH(NOLOCK) ON c901.unges = c913.ungesreq
798 AND c901.numreg = c913.numreq
799 AND c901.anoreg = c913.anoreq
800 AND c901.estrut = c913.estrut
801 AND c901.grupo = c913.grupo
802 AND c901.subgrp = c913.subgrp
803 AND c901.itemat = c913.itemat
804 AND c901.digmat = c913.digmat
805 JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c909.nivel1
806 AND tmp.NivelIni2 = c909.nivel2
807 AND tmp.NivelIni3 = c909.nivel3
808 AND tmp.NivelIni4 = c909.nivel4
809 AND tmp.NivelIni5 = c909.nivel5
810 AND tmp.AnoIni = c909.anosc
811 WHERE c912.unges = c909.ungesAta
812 -- AND c913.ungesreq = c909.unges
813 AND c912.sigla = c906.sigla
814 AND c912.convit = c906.convit
815 AND c912.anoc = c906.anoc )
816 GROUP BY c906.unges, c906.nroata, c906.anoata
817 ,tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5
818 ,c911.estrut, c911.grupo, c911.subgrp, c911.itemat, c911.digmat
819 ,c909.unges, c909.numsc, c909.anosc, c906.sigla, c906.convit, c906.anoc, c911.idLote
820
821 -------------------------------------------------------------------------------------
822 -- Aditamentos com a unidade requisitante sem solicitação de compras
823 INSERT INTO @TMP_SALDO_REQUISITANTE(UngesAta, NroAta, AnoAta, Nivel1, Nivel2, Nivel3, Nivel4, Nivel5
824 ,Estrut, Grupo, Subgrp, Itemat, Digmat, Qtde_Requisitada, Qtde_Utilizada, Qtde_Entregue, Qtde_Cancelada, Qtde_Saldo
825 ,UngesSC, NumSC, AnoSC, Sigla, Convit, Anoc, idLote)
826 -- Somente o aditivo que não houver solicitação de entrega para a unidade requisitante.
827 SELECT UngesAta = c906.unges, NroAta = c906.nroata, AnoAta = c906.anoata
828 ,tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5
829 ,Estrut = c919.estrut
830 ,Grupo = c919.grupo
831 ,Subgrp = c919.subgrp
832 ,Itemat = c919.itemat
833 ,Digmat = c919.digmat
834 ,Qtde_Requisitada = 0
835 ,Qtde_Utilizada = 0
836 ,Qtde_Entregue = 0
837 ,Qtde_Cancelada = 0
838 ,Qtde_Saldo = 0
839 ,UngesSC = NULL
840 ,NumSC = NULL
841 ,AnoSC = NULL
842 ,Sigla = c906.sigla
843 ,Convit = c906.convit
844 ,Anoc = c906.anoc
845 ,idLote = c919.idLote
846 -- Ata
847 FROM MCT90600 c906 WITH(NOLOCK)
848 -- Itens da Ata
849 INNER JOIN MCT90700 c907 WITH(NOLOCK)
850 ON c907.unges = c906.unges
851 AND c907.nroata = c906.nroata
852 AND c907.anoata = c906.anoata
853 -- Aditamentos
854 INNER JOIN MCT91900 c919 WITH(NOLOCK)
855 ON c919.unges = c906.unges
856 AND c919.nroata = c906.nroata
857 AND c919.anoata = c906.anoata
858 AND c919.estrut = c907.estrut
859 AND c919.grupo = c907.grupo
860 AND c919.subgrp = c907.subgrp
861 AND c919.itemat = c907.itemat
862 AND c919.digmat = c907.digmat
863 AND ISNULL(c919.idLote,0) = ISNULL(c907.idLote,0)
864
865 -- Troca de exercÃcio/unidade orçamentária
866 JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c919.nivel1
867 AND tmp.NivelIni2 = c919.nivel2
868 AND tmp.NivelIni3 = c919.nivel3
869 AND tmp.NivelIni4 = c919.nivel4
870 AND tmp.NivelIni5 = c919.nivel5
871 AND tmp.AnoIni = YEAR(c919.dtadit)
872
873 WHERE NOT EXISTS( SELECT 1
874 -- Agrupamento
875 FROM MCT91200 c912b WITH(NOLOCK)
876 -- Itens do agrupamento
877 INNER JOIN MCT91300 c913b WITH(NOLOCK)
878 ON c913b.unges = c912b.unges
879 AND c913b.codgrupo = c912b.codgrupo
880 AND c913b.anogrupo = c912b.anogrupo
881 -- RC / RP
882 INNER JOIN MCT90000 c900b WITH(NOLOCK)
883 ON c900b.unges = c913b.ungesreq
884 AND c900b.numreg = c913b.numreq
885 AND c900b.anoreg = c913b.anoreq
886 -- Itens da RC / RP
887 INNER JOIN MCT90100 c901b WITH(NOLOCK)
888 ON c901b.unges = c900b.unges
889 AND c901b.numreg = c900b.numreg
890 AND c901b.anoreg = c900b.anoreg
891 AND c901b.estrut = c913b.estrut
892 AND c901b.grupo = c913b.grupo
893 AND c901b.subgrp = c913b.subgrp
894 AND c901b.itemat = c913b.itemat
895 AND c901b.digmat = c913b.digmat
896 -- Troca de exercÃcio/unidade orçamentária
897 JOIN @TMP_SECRETARIAS tmpb ON tmpb.NivelIni1 = c900b.nivel1
898 AND tmpb.NivelIni2 = c900b.nivel2
899 AND tmpb.NivelIni3 = c900b.nivel3
900 AND tmpb.NivelIni4 = c900b.nivel4
901 AND tmpb.NivelIni5 = c900b.nivel5
902 AND tmpb.AnoIni = c900b.anoreg
903 WHERE c912b.unges = c906.unges
904 AND c912b.sigla = c906.sigla
905 AND c912b.convit = c906.convit
906 AND c912b.anoc = c906.anoc
907 AND tmpb.NivelFim1 = tmp.NivelFim1
908 AND tmpb.NivelFim2 = tmp.NivelFim2
909 AND tmpb.NivelFim3 = tmp.NivelFim3
910 AND tmpb.NivelFim4 = tmp.NivelFim4
911 AND tmpb.NivelFim5 = tmp.NivelFim5
912 AND tmpb.AnoFim = tmp.AnoFim
913 AND c913b.estrut = c907.estrut
914 AND c913b.grupo = c907.grupo
915 AND c913b.subgrp = c907.subgrp
916 AND c913b.itemat = c907.itemat
917 AND c913b.digmat = c907.digmat
918 AND ISNULL(c913b.idLote,0) = ISNULL(c907.idLote,0))
919
920 AND NOT EXISTS( SELECT 1
921 -- SC
922 FROM MCT90900 c909 WITH(NOLOCK)
923 -- Itens da SC
924 INNER JOIN MCT91100 c911 WITH(NOLOCK)
925 ON c911.unges = c909.unges
926 AND c911.numsc = c909.numsc
927 AND c911.anosc = c909.anosc
928 AND c911.estrut = c919.estrut
929 AND c911.grupo = c919.grupo
930 AND c911.subgrp = c919.subgrp
931 AND c911.itemat = c919.itemat
932 AND c911.digmat = c919.digmat
933 AND ISNULL(c911.idLote,0) = ISNULL(c919.idLote,0)
934 AND c906.unges = CASE WHEN @p_unges IS NOT NULL THEN @p_unges ELSE c906.unges END
935 AND c906.nroata = CASE WHEN @p_nroata IS NOT NULL THEN @p_nroata ELSE c906.nroata END
936 AND c906.anoata = CASE WHEN @p_anoata IS NOT NULL THEN @p_anoata ELSE c906.anoata END
937 WHERE c909.ungesata = c906.unges
938 AND c909.nroata = c906.nroata
939 AND c909.anoata = c906.anoata
940 AND c909.nivel1 = tmp.NivelFim1
941 AND c909.nivel2 = tmp.NivelFim2
942 AND c909.nivel3 = tmp.NivelFim3
943 AND c909.nivel4 = tmp.NivelFim4
944 AND c909.nivel5 = tmp.NivelFim5
945 AND c909.datsol >= CASE WHEN @p_DtSolicInicial IS NOT NULL THEN @p_DtSolicInicial ELSE c909.datsol END
946 AND c909.datsol <= CASE WHEN @p_DtSolicFinal IS NOT NULL THEN @p_DtSolicFinal ELSE c909.datsol END
947 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
948 )
949
950
951 AND c906.unges = CASE WHEN @p_unges IS NULL THEN c906.unges ELSE @p_unges END
952 AND c906.nroata = CASE WHEN @p_nroata IS NULL THEN c906.nroata ELSE @p_nroata END
953 AND c906.anoata = CASE WHEN @p_anoata IS NULL THEN c906.anoata ELSE @p_anoata END
954
955 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 >=
956 CASE WHEN @p_UnidOrcInicial IS NOT NULL THEN @p_UnidOrcInicial
957 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
958
959 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 <=
960 CASE WHEN @p_UnidOrcFinal IS NOT NULL THEN @p_UnidOrcFinal
961 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
962
963 AND c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
964 THEN @p_MaterialInicial
965 ELSE c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat
966 END
967
968 AND c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
969 THEN @p_MaterialFinal
970 ELSE c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat
971 END
972 GROUP BY c906.unges, c906.nroata, c906.anoata, tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5
973 ,c919.estrut, c919.grupo, c919.subgrp, c919.itemat, c919.digmat
974 ,c906.sigla, c906.convit, c906.anoc, c919.idLote
975
976
977
978 -------------------------------------------------------------------------------------
979 -- Quantidade Requisitada (Quantidade Requisitada e Quantidade Aditada)
980 -- Sem Solicitação de Compras
981 INSERT INTO @TMP_SALDO_REQUISITANTE(UngesAta, NroAta, AnoAta, Nivel1, Nivel2, Nivel3, Nivel4, Nivel5
982 ,Estrut, Grupo, Subgrp, Itemat, Digmat, Qtde_Requisitada, Qtde_Utilizada, Qtde_Entregue, Qtde_Cancelada, Qtde_Saldo
983 ,UngesSC, NumSC, AnoSC, Sigla, Convit, Anoc, idLote)
984 SELECT UngesAta = c906.unges, NroAta = c906.nroata, AnoAta = c906.anoata
985 ,tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5
986 ,Estrut = c907.estrut
987 ,Grupo = c907.grupo
988 ,Subgrp = c907.subgrp
989 ,Itemat = c907.itemat
990 ,Digmat = c907.digmat
991-- ,Qtde_Requisitada = SUM(c901.quatde) -- Quantidade requisitada
992 ,Qtde_Requisitada = 0 -- Quantidade requisitada
993 ,Qtde_Utilizada = 0
994 ,Qtde_Entregue = 0
995 ,Qtde_Cancelada = 0
996 ,Qtde_Saldo = 0
997 ,UngesSC = NULL
998 ,NumSC = NULL
999 ,AnoSC = NULL
1000 ,Sigla = c906.sigla
1001 ,Convit = c906.convit
1002 ,Anoc = c906.anoc
1003 ,idLote = c907.idLote
1004 -- Ata
1005 FROM MCT90600 c906 WITH(NOLOCK)
1006 -- Itens da Ata
1007 INNER JOIN MCT90700 c907 WITH(NOLOCK) ON c907.unges = c906.unges
1008 AND c907.nroata = c906.nroata
1009 AND c907.anoata = c906.anoata
1010 -- Agrupamento
1011 INNER JOIN MCT91200 c912 WITH(NOLOCK) ON c912.unges = c906.unges
1012 AND c912.sigla = c906.sigla
1013 AND c912.convit = c906.convit
1014 AND c912.anoc = c906.anoc
1015 -- Itens do agrupamento
1016 INNER JOIN MCT91300 c913 WITH(NOLOCK) ON c913.unges = c912.unges
1017 AND c913.codgrupo = c912.codgrupo
1018 AND c913.anogrupo = c912.anogrupo
1019 AND c913.estrut = c907.estrut
1020 AND c913.grupo = c907.grupo
1021 AND c913.subgrp = c907.subgrp
1022 AND c913.itemat = c907.itemat
1023 AND c913.digmat = c907.digmat
1024 AND ISNULL(c913.idLote,0) = ISNULL(c907.idLote,0)
1025 -- RC / RP
1026 INNER JOIN MCT90000 c900 WITH(NOLOCK) ON c900.unges = c913.ungesreq
1027 AND c900.numreg = c913.numreq
1028 AND c900.anoreg = c913.anoreq
1029 -- Itens da RC / RP
1030 INNER JOIN MCT90100 c901 WITH(NOLOCK) ON c901.unges = c900.unges
1031 AND c901.numreg = c900.numreg
1032 AND c901.anoreg = c900.anoreg
1033 AND c901.estrut = c913.estrut
1034 AND c901.grupo = c913.grupo
1035 AND c901.subgrp = c913.subgrp
1036 AND c901.itemat = c913.itemat
1037 AND c901.digmat = c913.digmat
1038 -- Troca de exercÃcio/unidade orçamentária
1039 JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c900.nivel1
1040 AND tmp.NivelIni2 = c900.nivel2
1041 AND tmp.NivelIni3 = c900.nivel3
1042 AND tmp.NivelIni4 = c900.nivel4
1043 AND tmp.NivelIni5 = c900.nivel5
1044 AND tmp.AnoIni = c900.anoreg
1045 WHERE c906.unges = CASE WHEN @p_unges IS NOT NULL THEN @p_unges ELSE c906.unges END
1046 AND c906.nroata = CASE WHEN @p_nroata IS NOT NULL THEN @p_nroata ELSE c906.nroata END
1047 AND c906.anoata = CASE WHEN @p_anoata IS NOT NULL THEN @p_anoata ELSE c906.anoata END
1048 AND NOT EXISTS( SELECT 1 FROM MCT90900 c909b WITH(NOLOCK)
1049 INNER JOIN MCT91100 c911b WITH(NOLOCK) ON c911b.unges = c909b.unges
1050 AND c911b.numsc = c909b.numsc
1051 AND c911b.anosc = c909b.anosc
1052 AND c911b.estrut = c907.estrut
1053 AND c911b.grupo = c907.grupo
1054 AND c911b.subgrp = c907.subgrp
1055 AND c911b.itemat = c907.itemat
1056 AND c911b.digmat = c907.digmat
1057 AND ISNULL(c911b.idLote,0) = ISNULL(c907.idLote,0)
1058 WHERE c909b.ungesata = c906.unges
1059 AND c909b.nroata = c906.nroata
1060 AND c909b.anoata = c906.anoata
1061 AND c909b.nivel1 = tmp.NivelFim1
1062 AND c909b.nivel2 = tmp.NivelFim2
1063 AND c909b.nivel3 = tmp.NivelFim3
1064 AND c909b.nivel4 = tmp.NivelFim4
1065 AND c909b.nivel5 = tmp.NivelFim5
1066 AND c909b.anosc = tmp.anofim
1067 AND ISNULL(c909b.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
1068 )
1069
1070/* WHERE c909b.ungesata = c906.unges
1071 AND c909b.nroata = c906.nroata
1072 AND c909b.anoata = c906.anoata
1073 AND c909b.nivel1 = tmp.NivelIni1
1074 AND c909b.nivel2 = tmp.NivelIni2
1075 AND c909b.nivel3 = tmp.NivelIni3
1076 AND c909b.nivel4 = tmp.NivelIni4
1077 AND c909b.nivel5 = tmp.NivelIni5
1078 AND c909b.anosc = tmp.anoini)
1079*/
1080
1081 AND NOT EXISTS (SELECT 1 FROM @TMP_SALDO_REQUISITANTE tmpb
1082 WHERE tmpb.UngesAta = c906.unges
1083 AND tmpb.NroAta = c906.nroata
1084 AND tmpb.AnoAta = c906.anoata
1085 AND tmpb.Nivel1 = tmp.NivelFim1
1086 AND tmpb.Nivel2 = tmp.NivelFim2
1087 AND tmpb.Nivel3 = tmp.NivelFim3
1088 AND tmpb.Nivel4 = tmp.NivelFim4
1089 AND tmpb.Nivel5 = tmp.NivelFim5
1090 AND tmpb.estrut = c907.estrut
1091 AND tmpb.grupo = c907.grupo
1092 AND tmpb.subgrp = c907.subgrp
1093 AND tmpb.itemat = c907.itemat
1094 AND tmpb.digmat = c907.digmat
1095 AND ISNULL(tmpb.idLote,0) = ISNULL(c907.idLote,0))
1096
1097 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 >=
1098 CASE WHEN @p_UnidOrcInicial IS NOT NULL THEN @p_UnidOrcInicial
1099 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
1100
1101 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 <=
1102 CASE WHEN @p_UnidOrcFinal IS NOT NULL THEN @p_UnidOrcFinal
1103 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
1104
1105 AND c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
1106 THEN @p_MaterialInicial
1107 ELSE c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat
1108 END
1109
1110 AND c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
1111 THEN @p_MaterialFinal
1112 ELSE c901.estrut + c901.grupo + c901.subgrp + c901.itemat + c901.digmat
1113 END
1114 GROUP BY c906.unges, c906.nroata, c906.anoata
1115 ,tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5
1116 ,c907.estrut, c907.grupo, c907.subgrp, c907.itemat, c907.digmat
1117 ,c906.sigla, c906.convit, c906.anoc, c907.idLote
1118
1119
1120 -------------------------------------------------------------------------------------
1121 -- Aditamento/Transferencia
1122UPDATE @TMP_MATERIAIS_REQUISITANTE
1123 SET Qtde_Requisitada = Qtde_Requisitada + rs.Qtde_Aditada
1124 FROM (SELECT UngesAtaRS = c919.unges, NroAtaRS = c919.nroata, AnoAtaRS = c919.anoata
1125 ,Nivel1RS = tmp.NivelFim1
1126 ,Nivel2RS = tmp.NivelFim2
1127 ,Nivel3RS = tmp.NivelFim3
1128 ,Nivel4RS = tmp.NivelFim4
1129 ,Nivel5RS = tmp.NivelFim5
1130 ,EstrutRS = c919.estrut
1131 ,GrupoRS = c919.grupo
1132 ,SubgrpRS = c919.subgrp
1133 ,ItematRs = c919.itemat
1134 ,DigmatRs = c919.digmat
1135 ,Qtde_Aditada = SUM(c919.qtde) -- Quantidade aditada
1136 ,idLoteRS = c919.idLote
1137 -- Aditamento
1138 FROM MCT91900 c919 WITH(NOLOCK)
1139 -- Troca de exercÃcio/unidade orçamentária
1140 JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c919.nivel1
1141 AND tmp.NivelIni2 = c919.nivel2
1142 AND tmp.NivelIni3 = c919.nivel3
1143 AND tmp.NivelIni4 = c919.nivel4
1144 AND tmp.NivelIni5 = c919.nivel5
1145 AND tmp.AnoIni = YEAR(c919.dtadit)
1146 WHERE c919.unges = CASE WHEN @p_unges IS NOT NULL THEN @p_unges ELSE c919.unges END
1147 AND c919.nroata = CASE WHEN @p_nroata IS NOT NULL THEN @p_nroata ELSE c919.nroata END
1148 AND c919.anoata = CASE WHEN @p_anoata IS NOT NULL THEN @p_anoata ELSE c919.anoata END
1149
1150
1151 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 >=
1152 CASE WHEN @p_UnidOrcInicial IS NOT NULL THEN @p_UnidOrcInicial
1153 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
1154
1155 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 <=
1156 CASE WHEN @p_UnidOrcFinal IS NOT NULL THEN @p_UnidOrcFinal
1157 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
1158
1159 AND c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
1160 THEN @p_MaterialInicial
1161 ELSE c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat
1162 END
1163
1164 AND c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
1165 THEN @p_MaterialFinal
1166 ELSE c919.estrut + c919.grupo + c919.subgrp + c919.itemat + c919.digmat
1167 END
1168
1169
1170 GROUP BY c919.unges, c919.nroata, c919.anoata,
1171 tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5,
1172 c919.estrut, c919.grupo, c919.subgrp, c919.itemat, c919.digmat, c919.idLote
1173 ) rs
1174 WHERE Nivel1 = rs.Nivel1RS
1175 AND Nivel2 = rs.Nivel2RS
1176 AND Nivel3 = rs.Nivel3RS
1177 AND Nivel4 = rs.Nivel4RS
1178 AND Nivel5 = rs.Nivel5RS
1179 AND Estrut = rs.EstrutRS
1180 AND Grupo = rs.GrupoRS
1181 AND Subgrp = rs.SubgrpRS
1182 AND Itemat = rs.ItematRS
1183 AND Digmat = rs.DigmatRS
1184 AND UngesAta = rs.UngesAtaRS
1185 AND ISNULL(idLote,0) = ISNULL(rs.idLoteRS,0)
1186-- AND NroAta = rs.NroAtaRS
1187-- AND AnoAta = rs.AnoAtaRS
1188
1189
1190 UPDATE @TMP_MATERIAIS_REQUISITANTE
1191 SET Qtde_Requisitada = Qtde_Requisitada + rs.Qtde
1192 FROM (SELECT UngesAtaRS = c906.unges, NroAtaRS = c906.nroata, AnoAtaRS = c906.anoata
1193 ,Nivel1RS = tmp.NivelFim1
1194 ,Nivel2RS = tmp.NivelFim2
1195 ,Nivel3RS = tmp.NivelFim3
1196 ,Nivel4RS = tmp.NivelFim4
1197 ,Nivel5RS = tmp.NivelFim5
1198 ,EstrutRS = c911.estrut
1199 ,GrupoRS = c911.grupo
1200 ,SubgrpRS = c911.subgrp
1201 ,ItematRs = c911.itemat
1202 ,DigmatRs = c911.digmat
1203 ,Qtde = SUM(c911.qtde)
1204 ,idLoteRS = c911.idLote
1205 -- Solicitação de Compras
1206 FROM MCT90900 c909 WITH(NOLOCK)
1207 -- Itens da Solicitação de Compras
1208 JOIN MCT91100 c911 WITH(NOLOCK) ON c911.unges = c909.unges
1209 AND c911.numsc = c909.numsc
1210 AND c911.anosc = c909.anosc
1211 -- Ata de registro de preços
1212 JOIN MCT90600 c906 WITH(NOLOCK) ON c906.unges = c909.ungesAta
1213 AND c906.nroata = c909.nroata
1214 AND c906.anoata = c909.anoata
1215 JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c909.nivel1
1216 AND tmp.NivelIni2 = c909.nivel2
1217 AND tmp.NivelIni3 = c909.nivel3
1218 AND tmp.NivelIni4 = c909.nivel4
1219 AND tmp.NivelIni5 = c909.nivel5
1220 AND tmp.AnoIni = c909.anosc
1221
1222 WHERE c909.UngesAta = CASE WHEN @p_unges IS NULL THEN c909.UngesAta ELSE @p_unges END
1223 AND c909.nroata = CASE WHEN @p_nroata IS NULL THEN c909.nroata ELSE @p_nroata END
1224 AND c909.anoata = CASE WHEN @p_anoata IS NULL THEN c909.anoata ELSE @p_anoata END
1225 AND c909.status IS NULL
1226 AND c909.nivel1ori IS NOT NULL
1227 AND c909.nivel1ori + c909.nivel2ori + c909.nivel3ori + c909.nivel4ori + c909.nivel5ori <> c909.nivel1 + c909.nivel2 + c909.nivel3 + c909.nivel4 + c909.nivel5
1228 AND c909.datsol >= CASE WHEN @p_DtSolicInicial IS NOT NULL THEN @p_DtSolicInicial ELSE c909.datsol END
1229 AND c909.datsol <= CASE WHEN @p_DtSolicFinal IS NOT NULL THEN @p_DtSolicFinal ELSE c909.datsol END
1230
1231 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
1232
1233 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 >=
1234 CASE WHEN @p_UnidOrcInicial IS NOT NULL THEN @p_UnidOrcInicial
1235 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
1236
1237 AND tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5 <=
1238 CASE WHEN @p_UnidOrcFinal IS NOT NULL THEN @p_UnidOrcFinal
1239 ELSE (tmp.NivelFim1 + tmp.NivelFim2 + tmp.NivelFim3 + tmp.NivelFim4 + tmp.NivelFim5) END
1240
1241 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
1242 THEN @p_MaterialInicial
1243 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
1244 END
1245
1246 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
1247 THEN @p_MaterialFinal
1248 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
1249 END
1250 GROUP BY c906.unges, c906.nroata, c906.anoata,
1251 tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5,
1252 c911.estrut, c911.grupo, c911.subgrp, c911.itemat, c911.digmat, c911.idLote
1253 ) rs
1254 WHERE Nivel1 = rs.Nivel1RS
1255 AND Nivel2 = rs.Nivel2RS
1256 AND Nivel3 = rs.Nivel3RS
1257 AND Nivel4 = rs.Nivel4RS
1258 AND Nivel5 = rs.Nivel5RS
1259 AND Estrut = rs.EstrutRS
1260 AND Grupo = rs.GrupoRS
1261 AND Subgrp = rs.SubgrpRS
1262 AND Itemat = rs.ItematRS
1263 AND Digmat = rs.DigmatRS
1264 AND ISNULL(idLote,0) = ISNULL(rs.idLoteRS,0)
1265 AND UngesAta = rs.UngesAtaRS
1266
1267
1268UPDATE @TMP_MATERIAIS_REQUISITANTE
1269 SET Qtde_Requisitada = Qtde_Requisitada + rs.Qtde
1270 FROM (SELECT UngesAtaRS = c906.unges, NroAtaRS = c906.nroata, AnoAtaRS = c906.anoata
1271 ,Nivel1RS = tmp.NivelFim1
1272 ,Nivel2RS = tmp.NivelFim2
1273 ,Nivel3RS = tmp.NivelFim3
1274 ,Nivel4RS = tmp.NivelFim4
1275 ,Nivel5RS = tmp.NivelFim5
1276 ,EstrutRS = c911.estrut
1277 ,GrupoRS = c911.grupo
1278 ,SubgrpRS = c911.subgrp
1279 ,ItematRs = c911.itemat
1280 ,DigmatRs = c911.digmat
1281 ,Qtde = (SUM(c911.qtde) * -1)
1282 ,idLoteRS = c911.idLote
1283 -- Solicitação de Compras
1284 FROM MCT90900 c909 WITH(NOLOCK)
1285 -- Itens da Solicitação de Compras
1286 JOIN MCT91100 c911 WITH(NOLOCK) ON c911.unges = c909.unges
1287 AND c911.numsc = c909.numsc
1288 AND c911.anosc = c909.anosc
1289 -- Ata de registro de preços
1290 JOIN MCT90600 c906 WITH(NOLOCK) ON c906.unges = c909.ungesAta
1291 AND c906.nroata = c909.nroata
1292 AND c906.anoata = c909.anoata
1293
1294 left JOIN @TMP_SECRETARIAS tmp ON tmp.NivelIni1 = c909.nivel1ori
1295 AND tmp.NivelIni2 = c909.nivel2ori
1296 AND tmp.NivelIni3 = c909.nivel3ori
1297 AND tmp.NivelIni4 = c909.nivel4ori
1298 AND tmp.NivelIni5 = c909.nivel5ori
1299 AND tmp.AnoIni = c909.anosc
1300 left JOIN @TMP_SECRETARIAS tmpb ON tmpb.NivelIni1 = c909.nivel1
1301 AND tmpb.NivelIni2 = c909.nivel2
1302 AND tmpb.NivelIni3 = c909.nivel3
1303 AND tmpb.NivelIni4 = c909.nivel4
1304 AND tmpb.NivelIni5 = c909.nivel5
1305 AND tmpb.AnoIni = c909.anosc
1306
1307 WHERE c909.UngesAta = CASE WHEN @p_unges IS NULL THEN c909.UngesAta ELSE @p_unges END
1308 AND c909.nroata = CASE WHEN @p_nroata IS NULL THEN c909.nroata ELSE @p_nroata END
1309 AND c909.anoata = CASE WHEN @p_anoata IS NULL THEN c909.anoata ELSE @p_anoata END
1310 AND c909.status IS NULL
1311 AND c909.nivel1ori IS NOT NULL
1312 AND c909.nivel1ori + c909.nivel2ori + c909.nivel3ori + c909.nivel4ori + c909.nivel5ori <> c909.nivel1 + c909.nivel2 + c909.nivel3 + c909.nivel4 + c909.nivel5
1313 AND c909.datsol >= CASE WHEN @p_DtSolicInicial IS NOT NULL THEN @p_DtSolicInicial ELSE c909.datsol END
1314 AND c909.datsol <= CASE WHEN @p_DtSolicFinal IS NOT NULL THEN @p_DtSolicFinal ELSE c909.datsol END
1315
1316 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
1317
1318 AND tmpb.NivelFim1 + tmpb.NivelFim2 + tmpb.NivelFim3 + tmpb.NivelFim4 + tmpb.NivelFim5 >=
1319 CASE WHEN @p_UnidOrcInicial IS NOT NULL THEN @p_UnidOrcInicial
1320 ELSE (tmpb.NivelFim1 + tmpb.NivelFim2 + tmpb.NivelFim3 + tmpb.NivelFim4 + tmpb.NivelFim5) END
1321
1322 AND tmpb.NivelFim1 + tmpb.NivelFim2 + tmpb.NivelFim3 + tmpb.NivelFim4 + tmpb.NivelFim5 <=
1323 CASE WHEN @p_UnidOrcFinal IS NOT NULL THEN @p_UnidOrcFinal
1324 ELSE (tmpb.NivelFim1 + tmpb.NivelFim2 + tmpb.NivelFim3 + tmpb.NivelFim4 + tmpb.NivelFim5) END
1325
1326 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat >= CASE WHEN @p_MaterialInicial IS NOT NULL
1327 THEN @p_MaterialInicial
1328 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
1329 END
1330
1331 AND c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat <= CASE WHEN @p_MaterialFinal IS NOT NULL
1332 THEN @p_MaterialFinal
1333 ELSE c911.estrut + c911.grupo + c911.subgrp + c911.itemat + c911.digmat
1334 END
1335 GROUP BY c906.unges, c906.nroata, c906.anoata,
1336 tmp.NivelFim1, tmp.NivelFim2, tmp.NivelFim3, tmp.NivelFim4, tmp.NivelFim5,
1337 c911.estrut, c911.grupo, c911.subgrp, c911.itemat, c911.digmat, c911.idLote
1338 ) rs
1339 WHERE Nivel1 = rs.Nivel1RS
1340 AND Nivel2 = rs.Nivel2RS
1341 AND Nivel3 = rs.Nivel3RS
1342 AND Nivel4 = rs.Nivel4RS
1343 AND Nivel5 = rs.Nivel5RS
1344 AND Estrut = rs.EstrutRS
1345 AND Grupo = rs.GrupoRS
1346 AND Subgrp = rs.SubgrpRS
1347 AND Itemat = rs.ItematRS
1348 AND Digmat = rs.DigmatRS
1349 AND UngesAta = rs.UngesAtaRS
1350 AND ISNULL(IdLote,0) = ISNULL(rs.idLoteRS,0)
1351
1352
1353 -------------------------------------------------------------------------------------
1354 -- Quantidade utilizada (SC sem cancelamento)
1355 UPDATE @TMP_SALDO_REQUISITANTE
1356 SET Qtde_Utilizada = ISNULL(rs.Qtde_UtilizadaRS, 0)
1357 ,Dt_UltSolicit = rs.Dt_UltSolicitRS
1358 ,Af = rs.AfRS
1359 ,Nafano = rs.NafanoRS
1360 ,Tipaf = rs.TipafRS
1361 FROM (SELECT UngesAtaRS = tmp.UngesAta, NroAtaRS = tmp.NroAta, AnoAtaRS = tmp.AnoAta
1362 ,Nivel1RS = tmp.Nivel1
1363 ,Nivel2RS = tmp.Nivel2
1364 ,Nivel3RS = tmp.Nivel3
1365 ,Nivel4RS = tmp.Nivel4
1366 ,Nivel5RS = tmp.Nivel5
1367 ,EstrutRS = tmp.Estrut, GrupoRS = tmp.Grupo, SubgrpRS = tmp.Subgrp, ItematRS = tmp.Itemat, DigmatRS = tmp.Digmat
1368 ,Qtde_UtilizadaRS = c911.qtde
1369 ,Dt_UltSolicitRS = c909.datsol
1370 ,NumSCRS = c909.numsc, AnoSCRS = c909.anosc, UngesSCRS = tmp.UngesSC
1371 ,AfRS = c670.af, NafanoRS = c670.nafano, TipafRS = c670.tipaf
1372 ,idLoteRS = c911.idLote
1373 FROM @TMP_SALDO_REQUISITANTE tmp
1374 -- SC
1375 INNER JOIN MCT90900 c909 WITH(NOLOCK) ON c909.ungesata = tmp.UngesAta
1376 AND c909.nroata = tmp.NroAta
1377 AND c909.anoata = tmp.AnoAta
1378 AND c909.unges = tmp.UngesSC
1379 AND c909.numsc = tmp.NumSC
1380 AND c909.anosc = tmp.AnoSC
1381 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
1382 -- Itens da SC
1383 INNER JOIN MCT91100 c911 WITH(NOLOCK) ON c911.unges = c909.unges
1384 AND c911.numsc = c909.numsc
1385 AND c911.anosc = c909.anosc
1386 AND c911.estrut = tmp.Estrut
1387 AND c911.grupo = tmp.Grupo
1388 AND c911.subgrp = tmp.Subgrp
1389 AND c911.itemat = tmp.Itemat
1390 AND c911.digmat = tmp.Digmat
1391 AND ISNULL(c911.idLote,0) = ISNULL(tmp.idLote,0)
1392 -- Autorização de Fornecimento
1393 LEFT JOIN MCT67000 c670 WITH(NOLOCK)
1394 ON c670.unges = tmp.UngesAta
1395 AND c670.codgrupo = tmp.NumSC
1396 AND c670.anogrupo = tmp.AnoSC
1397 AND c670.sigla = tmp.Sigla
1398 AND c670.convit = tmp.Convit
1399 AND c670.anoc = tmp.Anoc) rs
1400 WHERE UngesAta = rs.UngesAtaRS
1401 AND NroAta = rs.NroAtaRS
1402 AND AnoAta = rs.AnoAtaRS
1403 AND Nivel1 = rs.Nivel1RS
1404 AND Nivel2 = rs.Nivel2RS
1405 AND Nivel3 = rs.Nivel3RS
1406 AND Nivel4 = rs.Nivel4RS
1407 AND Nivel5 = rs.Nivel5RS
1408 AND Estrut = rs.EstrutRS
1409 AND Grupo = rs.GrupoRS
1410 AND Subgrp = rs.SubgrpRS
1411 AND Itemat = rs.ItematRS
1412 AND Digmat = rs.DigmatRS
1413 AND NumSC = rs.NumSCRS
1414 AND AnoSC = rs.AnoSCRS
1415 AND UngesSC = rs.UngesSCRS
1416 AND ISNULL(idLote,0) = ISNULL(rs.idLoteRS,0)
1417
1418 -------------------------------------------------------------------------------------
1419
1420 ---- Quantidade Entregue
1421 UPDATE @TMP_SALDO_REQUISITANTE
1422 SET Qtde_Entregue = RS.Qtde_EntregueRS
1423 FROM (SELECT Qtde_EntregueRS = SUM(ISNULL(Qtde_Entregue,0) + ISNULL(e691.Qtde,0))
1424 ,UngesSCRS = tmp.UngesSC, AfRS = tmp.Af, NafanoRS = tmp.Nafano
1425 ,EstrutRS = tmp.Estrut, GrupoRS = tmp.Grupo, SubgrpRS = tmp.Subgrp
1426 ,ItematRS = tmp.Itemat, DigmatRS = tmp.Digmat, idLoteRS = tmp.idLote
1427 FROM @TMP_SALDO_REQUISITANTE tmp
1428 INNER JOIN MET68900 e689 WITH(NOLOCK) ON e689.unges = tmp.UngesSC
1429 AND e689.af = tmp.Af
1430 AND e689.nafano = tmp.Nafano
1431 AND ISNULL(e689.status,'') = ''
1432 INNER JOIN MET69100 e691 WITH(NOLOCK) ON e691.brmnum = e689.brmnum
1433 AND e691.brmano = e689.brmano
1434 AND e691.estrut = tmp.Estrut
1435 AND e691.grupo = tmp.Grupo
1436 AND e691.subgrp = tmp.Subgrp
1437 AND e691.itemat = tmp.Itemat
1438 AND e691.digmat = tmp.Digmat
1439 GROUP BY tmp.UngesSC, tmp.Af, tmp.Nafano
1440 ,tmp.Estrut, tmp.Grupo, tmp.Subgrp, tmp.Itemat, tmp.Digmat, tmp.idLote) RS
1441 WHERE UngesSC = RS.UngesSCRS
1442 AND Af = RS.AfRS
1443 AND Nafano = RS.NafanoRS
1444 AND Estrut = RS.EstrutRS
1445 AND Grupo = RS.GrupoRS
1446 AND Subgrp = RS.SubgrpRS
1447 AND Itemat = RS.ItematRS
1448 AND Digmat = RS.DigmatRS
1449 AND ISNULL(idLote,0) = ISNULL(RS.idLoteRS,0)
1450
1451
1452-----------------------------------------------------------------------------------------
1453
1454 -- Quantidade cancelada
1455 UPDATE @TMP_SALDO_REQUISITANTE
1456 SET Qtde_Cancelada = ISNULL(rs.Qtde_CanceladaRS,0) - rs.Qtde_Entregue
1457 FROM (SELECT UngesAtaRS = tmp.UngesAta, NroAtaRS = tmp.NroAta, AnoAtaRS = tmp.AnoAta
1458 ,NumSCRS = tmp.NumSC, AnoSCRS = tmp.AnoSC, UngesSCRS = tmp.UngesSC
1459 ,Nivel1RS = tmp.Nivel1, Nivel2RS = tmp.Nivel2, Nivel3RS = tmp.Nivel3, Nivel4RS = tmp.Nivel4, Nivel5RS = tmp.Nivel5
1460 ,EstrutRS = tmp.Estrut, GrupoRS = tmp.Grupo, SubgrpRS = tmp.Subgrp, ItematRS = tmp.Itemat, DigmatRS = tmp.Digmat, idLoteRS = tmp.idLote
1461 ,Qtde_CanceladaRS = c911.qtde, tmp.Qtde_Entregue
1462 FROM @TMP_SALDO_REQUISITANTE tmp
1463 -- SC
1464 INNER JOIN MCT90900 c909 WITH(NOLOCK) ON c909.ungesata = tmp.UngesAta
1465 AND c909.nroata = tmp.NroAta
1466 AND c909.anoata = tmp.AnoAta
1467 AND c909.unges = tmp.UngesSC
1468 AND c909.numsc = tmp.NumSC
1469 AND c909.anosc = tmp.AnoSC
1470 -- Itens da SC
1471 INNER JOIN MCT91100 c911 WITH(NOLOCK) ON c911.unges = c909.unges
1472 AND c911.numsc = c909.numsc
1473 AND c911.anosc = c909.anosc
1474 AND c911.estrut = tmp.Estrut
1475 AND c911.grupo = tmp.Grupo
1476 AND c911.subgrp = tmp.Subgrp
1477 AND c911.itemat = tmp.Itemat
1478 AND c911.digmat = tmp.Digmat
1479 AND ISNULL(c911.idLote,0) = ISNULL(tmp.idLote,0)
1480 WHERE ISNULL(c909.ocorrencia, '') <> ''
1481 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual)
1482 ) rs
1483 WHERE UngesAta = rs.UngesAtaRS
1484 AND NroAta = rs.NroAtaRS
1485 AND AnoAta = rs.AnoAtaRS
1486 AND UngesSC = rs.UngesSCRS
1487 AND NumSC = rs.NumSCRS
1488 AND AnoSC = rs.AnoSCRS
1489 AND Nivel1 = rs.Nivel1RS
1490 AND Nivel2 = rs.Nivel2RS
1491 AND Nivel3 = rs.Nivel3RS
1492 AND Nivel4 = rs.Nivel4RS
1493 AND Nivel5 = rs.Nivel5RS
1494 AND Estrut = rs.EstrutRS
1495 AND Grupo = rs.GrupoRS
1496 AND Subgrp = rs.SubgrpRS
1497 AND Itemat = rs.ItematRS
1498 AND Digmat = rs.DigmatRS
1499 AND ISNULL(idLote,0) = ISNULL(rs.idLoteRS,0)
1500
1501-----------------
1502-- QTD SUPRIMIDA
1503-----------------
1504IF (@habilitaIntegracaoCP = 1 )
1505BEGIN
1506 UPDATE @TMP_SALDO_REQUISITANTE
1507 SET Qtde_Suprimida = rs.Qtd_Suprimida, VlrFinanc_Suprimido = rs.VlrFinanc_Suprimido
1508 FROM ( SELECT Qtd_Suprimida = SUM(ISNULL(c008.Quantidade, 0)),
1509 VlrFinanc_Suprimido = SUM(ISNULL(c008.VlrTotal, 0)),
1510 estrutX = tmp.estrut, grupoX = tmp.grupo, subgrpX = tmp.subgrp, itematX = tmp.itemat, digmatX = tmp.digmat,
1511 Nivel1x = tmp.nivel1, Nivel2x = tmp.nivel2, Nivel3x = tmp.nivel3, Nivel4x = tmp.nivel4, Nivel5x = tmp.nivel5,
1512 numscx = tmp.numsc, anoscx = tmp.anosc, idLotex = tmp.idLote
1513 FROM @TMP_SALDO_REQUISITANTE tmp
1514 INNER JOIN MCT80400 c804 WITH (READUNCOMMITTED) ON c804.unges = tmp.ungesSC
1515 AND c804.codgrupo = tmp.numsc
1516 AND c804.anogrupo = tmp.anosc
1517 INNER JOIN MCT80100 c801 WITH (READUNCOMMITTED) ON c801.unges = c804.unges
1518 AND c801.sigla = c804.sigla
1519 AND c801.convit = c804.convit
1520 AND c801.anoc = c804.anoc
1521 INNER JOIN MCT67000 c670 WITH (READUNCOMMITTED) ON c670.unges = c801.unges
1522 AND c670.numint = c801.numint
1523 AND c670.anoint = c801.anoint
1524 AND c670.codgrupo = c804.codgrupo
1525 AND c670.anogrupo = c804.anogrupo
1526 INNER JOIN MCT66800 c668 WITH (READUNCOMMITTED) ON c668.unges = c670.unges
1527 AND c668.numint = c670.numint
1528 AND c668.anoint = c670.anoint
1529 AND c668.af = c670.af
1530 AND c668.nafano = c670.nafano
1531 AND c668.estrut = tmp.estrut
1532 AND c668.grupo = tmp.grupo
1533 AND c668.subgrp = tmp.subgrp
1534 AND c668.itemat = tmp.itemat
1535 AND c668.digmat = tmp.digmat
1536 INNER JOIN MCT00800 c008 WITH (READUNCOMMITTED) ON c008.IdAFOS = c670.id
1537 AND c008.IdItemAFOS = c668.IdItemAF
1538 AND c008.status NOT IN ('D','C')
1539 INNER JOIN MCT90600 c906 WITH (READUNCOMMITTED) ON c906.unges = c670.unges
1540 AND c906.convit = c670.convit
1541 AND c906.sigla = c670.sigla
1542 AND c906.anoc = c670.anoc
1543 LEFT JOIN MCT90900 c909 WITH(NOLOCK) ON c909.ungesata = tmp.UngesAta
1544 AND c909.nroata = tmp.NroAta
1545 AND c909.anoata = tmp.AnoAta
1546 AND c909.unges = tmp.UngesSC
1547 AND c909.numsc = tmp.NumSC
1548 AND c909.anosc = tmp.AnoSC
1549 WHERE c906.unges = CASE WHEN @p_unges IS NOT NULL THEN @p_unges ELSE c906.unges END
1550 AND c906.nroata = CASE WHEN @p_nroata IS NOT NULL THEN @p_nroata ELSE c906.nroata END
1551 AND c906.anoata = CASE WHEN @p_anoata IS NOT NULL THEN @p_anoata ELSE c906.anoata END
1552 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
1553 --AND c008.tipoaltcontrato IN (3, 4)
1554 AND (ISNULL(c909.ocorrencia, '') = '' OR ISNULL(c670.ocorrencia, '') = '')
1555 AND EXISTS( SELECT 1
1556 FROM MCT91200
1557 WHERE unges = c804.unges
1558 AND sigla = c804.sigla
1559 AND convit = c804.convit
1560 AND anoc = c804.anoc )
1561 GROUP BY tmp.estrut, tmp.grupo, tmp.subgrp, tmp.itemat, tmp.digmat,c008.tipoaltcontrato,
1562 tmp.nivel1, tmp.nivel2, tmp.nivel3, tmp.nivel4, tmp.nivel5, tmp.numsc, tmp.anosc, tmp.idLote, c668.preco ) rs
1563 WHERE estrut = rs.estrutX
1564 AND grupo = rs.grupoX
1565 AND subgrp = rs.subgrpX
1566 AND itemat = rs.itematX
1567 AND digmat = rs.digmatX
1568 AND nivel1 = rs.nivel1X
1569 AND nivel2 = rs.nivel2X
1570 AND nivel3 = rs.nivel3X
1571 AND nivel4 = rs.nivel4X
1572 AND nivel5 = rs.nivel5X
1573 AND numsc = rs.numscx
1574 AND anosc = rs.anoscx
1575 AND ISNULL(idLote,0) = ISNULL(rs.idLotex,0)
1576END
1577ELSE
1578BEGIN
1579 UPDATE @TMP_SALDO_REQUISITANTE
1580 SET Qtde_Suprimida = rs.Qtd_Suprimida, VlrFinanc_Suprimido = rs.VlrFinanc_Suprimido
1581 FROM ( SELECT Qtd_Suprimida = SUM(ISNULL(c669.qtde, 0)),
1582 VlrFinanc_Suprimido = CASE
1583 WHEN c669.tipoaltcontrato = 3 THEN SUM(ISNULL(c669.qtde, 0) * ISNULL(c668.preco,0))
1584 WHEN c669.tipoaltcontrato = 4 THEN SUM(ISNULL(c669.valor, 0))
1585 END,
1586 estrutX = c669.estrut, grupoX = c669.grupo, subgrpX = c669.subgrp, itematX = c669.itemat, digmatX = c669.digmat,
1587 Nivel1x = tmp.nivel1, Nivel2x = tmp.nivel2, Nivel3x = tmp.nivel3, Nivel4x = tmp.nivel4, Nivel5x = tmp.nivel5,
1588 numscx = tmp.numsc, anoscx = tmp.anosc, idLotex = tmp.idLote
1589 FROM @TMP_SALDO_REQUISITANTE tmp
1590 INNER JOIN MCT80400 c804 WITH (READUNCOMMITTED) ON c804.unges = tmp.ungesSC
1591 AND c804.codgrupo = tmp.numsc
1592 AND c804.anogrupo = tmp.anosc
1593 INNER JOIN MCT80100 c801 WITH (READUNCOMMITTED) ON c801.unges = c804.unges
1594 AND c801.sigla = c804.sigla
1595 AND c801.convit = c804.convit
1596 AND c801.anoc = c804.anoc
1597 INNER JOIN MCT67000 c670 WITH (READUNCOMMITTED) ON c670.unges = c801.unges
1598 AND c670.numint = c801.numint
1599 AND c670.anoint = c801.anoint
1600 AND c670.codgrupo = c804.codgrupo
1601 AND c670.anogrupo = c804.anogrupo
1602 INNER JOIN MCT66800 c668 WITH (READUNCOMMITTED) ON c668.unges = c670.unges
1603 AND c668.numint = c670.numint
1604 AND c668.anoint = c670.anoint
1605 AND c668.af = c670.af
1606 AND c668.nafano = c670.nafano
1607 AND c668.estrut = tmp.estrut
1608 AND c668.grupo = tmp.grupo
1609 AND c668.subgrp = tmp.subgrp
1610 AND c668.itemat = tmp.itemat
1611 AND c668.digmat = tmp.digmat
1612 INNER JOIN MCT66900 c669 WITH (READUNCOMMITTED) ON c669.unges = c670.unges
1613 AND c669.numint = c670.numint
1614 AND c669.anoint = c670.anoint
1615 AND c669.estrut = c668.estrut
1616 AND c669.grupo = c668.grupo
1617 AND c669.subgrp = c668.subgrp
1618 AND c669.itemat = c668.itemat
1619 AND c669.digmat = c668.digmat
1620 INNER JOIN MCT90600 c906 WITH (READUNCOMMITTED) ON c906.unges = c670.unges
1621 AND c906.convit = c670.convit
1622 AND c906.sigla = c670.sigla
1623 AND c906.anoc = c670.anoc
1624 LEFT JOIN MCT90900 c909 WITH(NOLOCK) ON c909.ungesata = tmp.UngesAta
1625 AND c909.nroata = tmp.NroAta
1626 AND c909.anoata = tmp.AnoAta
1627 AND c909.unges = tmp.UngesSC
1628 AND c909.numsc = tmp.NumSC
1629 AND c909.anosc = tmp.AnoSC
1630 WHERE c906.unges = CASE WHEN @p_unges IS NOT NULL THEN @p_unges ELSE c906.unges END
1631 AND c906.nroata = CASE WHEN @p_nroata IS NOT NULL THEN @p_nroata ELSE c906.nroata END
1632 AND c906.anoata = CASE WHEN @p_anoata IS NOT NULL THEN @p_anoata ELSE c906.anoata END
1633 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
1634 AND c669.tipoaltcontrato IN (3, 4)
1635 AND (ISNULL(c909.ocorrencia, '') = '' OR ISNULL(c670.ocorrencia, '') = '')
1636 AND EXISTS( SELECT 1
1637 FROM MCT91200
1638 WHERE unges = c804.unges
1639 AND sigla = c804.sigla
1640 AND convit = c804.convit
1641 AND anoc = c804.anoc )
1642 GROUP BY c669.estrut, c669.grupo,c669.subgrp, c669.itemat, c669.digmat, c669.tipoaltcontrato,
1643 tmp.nivel1, tmp.nivel2, tmp.nivel3, tmp.nivel4, tmp.nivel5, tmp.numsc, tmp.anosc, tmp.idLote, c668.preco) rs
1644 WHERE estrut = rs.estrutX
1645 AND grupo = rs.grupoX
1646 AND subgrp = rs.subgrpX
1647 AND itemat = rs.itematX
1648 AND digmat = rs.digmatX
1649 AND nivel1 = rs.nivel1X
1650 AND nivel2 = rs.nivel2X
1651 AND nivel3 = rs.nivel3X
1652 AND nivel4 = rs.nivel4X
1653 AND nivel5 = rs.nivel5X
1654 AND numsc = rs.numscx
1655 AND anosc = rs.anoscx
1656 AND ISNULL(idLote,0) = ISNULL(rs.idLotex,0)
1657END
1658
1659 -------------------------------------------------------------------------------------
1660 -- Atualiza os valores dos itens
1661 UPDATE @TMP_SALDO_REQUISITANTE
1662 SET Vlr_Unitario = rs.Vlr_UnitarioRS
1663 FROM (SELECT NroAtaRS = tmp.NroAta, AnoAtaRS = tmp.AnoAta, UngesAtaRS = tmp.UngesAta
1664 ,MaterialRS = tmp.Estrut + tmp.Grupo + tmp.Subgrp + tmp.Itemat + tmp.Digmat
1665 ,Vlr_UnitarioRS = ISNULL(c911.valite, c907.pr_unit)
1666 ,idLoteRS = tmp.idLote
1667 FROM @TMP_SALDO_REQUISITANTE tmp
1668 -- Ata RP
1669 INNER JOIN MCT90600 c906 WITH(NOLOCK) ON c906.unges = tmp.UngesAta
1670 AND c906.nroata = tmp.nroata
1671 AND c906.anoata = tmp.anoata
1672 -- Proposta do fornecedor
1673 INNER JOIN MCT90700 c907 WITH(NOLOCK) ON c907.unges = c906.unges
1674 --AND c905.sigla = c906.sigla
1675 --AND c905.convit = c906.convit
1676 --AND c905.anoc = c906.anoc
1677 --AND c905.codfor = c906.codfor
1678 AND c907.nroata = c906.nroata
1679 AND c907.anoata = c906.anoata
1680 AND c907.estrut = tmp.Estrut
1681 AND c907.grupo = tmp.Grupo
1682 AND c907.subgrp = tmp.Subgrp
1683 AND c907.itemat = tmp.Itemat
1684 AND c907.digmat = tmp.Digmat
1685 AND ISNULL(c907.idLote,0) = ISNULL(tmp.idLote,0)
1686 AND c907.class = c906.class
1687 -- SC
1688 LEFT JOIN MCT90900 c909 WITH(NOLOCK) ON c909.ungesata = c906.unges
1689 AND c909.nroata = c906.nroata
1690 AND c909.anoata = c906.anoata
1691 AND c909.numsc = tmp.NumSC
1692 AND c909.anosc = tmp.AnoSC
1693 AND ISNULL(c909.IdAditivo, 0) <= 0 -- Alteração referente a Renovação Contratual
1694 LEFT JOIN MCT91100 c911 WITH(NOLOCK) ON c911.unges = c909.unges
1695 AND c911.numsc = c909.numsc
1696 AND c911.anosc = c909.anosc
1697 AND c911.estrut = c907.estrut
1698 AND c911.grupo = c907.grupo
1699 AND c911.subgrp = c907.subgrp
1700 AND c911.digmat = c907.digmat
1701 AND c911.itemat = c907.itemat
1702 AND ISNULL(c911.idLote,0) = ISNULL(c907.idLote,0) )rs
1703
1704 WHERE Estrut + Grupo + Subgrp + Itemat + Digmat = rs.MaterialRS
1705 AND NroAta = rs.NroAtaRS
1706 AND AnoAta = rs.AnoAtaRS
1707 AND UngesAta = rs.UngesAtaRS
1708 AND ISNULL(idLote,0) = ISNULL(rs.idLoteRS,0)
1709
1710 DECLARE
1711 @Ido INT
1712 ,@IdoOld INT
1713 ,@Estrut CHAR(1)
1714 ,@Grupo CHAR(2)
1715 ,@Subgrp CHAR(2)
1716 ,@Itemat CHAR(4)
1717 ,@Digmat CHAR(1)
1718 ,@Dt_UltSolicit DATETIME
1719 ,@Qtde_Saldo DECIMAL(15,3)
1720 ,@IdLote INT
1721
1722 SET @Qtde_Saldo = 0
1723 SET @IdoOld = 0
1724
1725 DECLARE CURSOR_ATA CURSOR FOR
1726 SELECT Ido, Estrut, Grupo, Subgrp, Itemat, Digmat, Dt_UltSolicit, idLote
1727 FROM @TMP_SALDO_REQUISITANTE
1728 ORDER BY Nivel1, Nivel2, Nivel3, Nivel4, Nivel5, Estrut, Grupo, Subgrp, Itemat, Digmat, Dt_UltSolicit
1729
1730 OPEN CURSOR_ATA
1731
1732 FETCH NEXT FROM CURSOR_ATA
1733 INTO @Ido, @Estrut, @Grupo, @Subgrp, @Itemat, @Digmat, @Dt_UltSolicit, @IdLote
1734
1735 WHILE @@FETCH_STATUS = 0
1736 BEGIN
1737
1738 UPDATE tmp SET tmp.Qtde_Requisitada = tmpb.Qtde_Requisitada
1739 FROM @TMP_SALDO_REQUISITANTE tmp
1740 JOIN @TMP_MATERIAIS_REQUISITANTE tmpb
1741 ON tmpb.nivel1 = tmp.nivel1
1742 AND tmpb.nivel2 = tmp.nivel2
1743 AND tmpb.nivel3 = tmp.nivel3
1744 AND tmpb.nivel4 = tmp.nivel4
1745 AND tmpb.nivel5 = tmp.nivel5
1746 AND tmpb.Estrut = tmp.Estrut
1747 AND tmpb.Grupo = tmp.Grupo
1748 AND tmpb.Subgrp = tmp.Subgrp
1749 AND tmpb.Itemat = tmp.Itemat
1750 AND tmpb.Digmat = tmp.Digmat
1751 AND ISNULL(tmpb.IdLote,0) = ISNULL(tmp.idLote,0)
1752 WHERE tmp.Ido = @Ido
1753
1754
1755 IF ( (SELECT Estrut+Grupo+Subgrp+Itemat+Digmat FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @Ido)
1756 <> (SELECT Estrut+Grupo+Subgrp+Itemat+Digmat FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @IdoOld) )
1757 OR ( (SELECT Nivel1+Nivel2+Nivel3+Nivel4+Nivel5 FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @Ido)
1758 <> (SELECT Nivel1+Nivel2+Nivel3+Nivel4+Nivel5 FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @IdoOld) )
1759 OR ( (SELECT ISNULL(IdLote,0) FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @Ido)
1760 <> (SELECT ISNULL(IdLote,0) FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @IdoOld) )
1761 BEGIN
1762 SET @Qtde_Saldo = 0
1763 END
1764
1765 IF (@Qtde_Saldo = 0)
1766 BEGIN
1767 SELECT @Qtde_Saldo = Qtde_Requisitada FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @Ido
1768 END
1769 DECLARE @Qtde_SaldoAnt DECIMAL(15,3)
1770 SET @Qtde_SaldoAnt = @Qtde_Saldo
1771
1772 SELECT @Qtde_Saldo = ( @Qtde_Saldo - Qtde_Utilizada ) + Qtde_Cancelada + ISNULL(Qtde_Suprimida, 0) FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @Ido
1773
1774 -- Acerta o saldo por ATA
1775 UPDATE @TMP_SALDO_REQUISITANTE
1776 SET Qtde_SaldoAnt = @Qtde_SaldoAnt
1777 ,Qtde_Saldo = @Qtde_Saldo
1778 --SET Qtde_Saldo = ( ISNULL( (SELECT TOP 1 Qtde_Saldo
1779 -- FROM @TMP_SALDO_ATA
1780 -- WHERE Estrut+Grupo+Subgrp+Itemat+Digmat = @Estrut+@Grupo+@Subgrp+@Itemat+@Digmat
1781 -- AND Dt_UltSolicit <= @Dt_UltSolicit
1782 -- AND Ido <> @Ido
1783 -- ORDER BY Dt_UltSolicit DESC ) , Qtde_Requisitada) - Qtde_Utilizada ) + Qtde_Cancelada
1784 ,Vlr_Total = Vlr_Total * Vlr_Unitario
1785 ,Saldo_Financeiro = (( Qtde_Requisitada - Qtde_Utilizada ) + Qtde_Cancelada + ISNULL(Qtde_Suprimida, 0)) * Vlr_Unitario
1786 WHERE Ido = @Ido
1787
1788 SELECT @Nivel1 = Nivel1, @Nivel2 = Nivel2, @Nivel3 = Nivel3, @Nivel4 = Nivel4, @Nivel5 = Nivel5 FROM @TMP_SALDO_REQUISITANTE WHERE Ido = @Ido
1789
1790 UPDATE @TMP_MATERIAIS_REQUISITANTE SET Qtde_Requisitada = @Qtde_Saldo
1791 where nivel1 = @Nivel1
1792 AND nivel2 = @Nivel2
1793 AND nivel3 = @Nivel3
1794 AND nivel4 = @Nivel4
1795 AND nivel5 = @Nivel5
1796 AND Estrut = @Estrut
1797 AND Grupo = @Grupo
1798 AND Subgrp = @Subgrp
1799 AND Itemat = @Itemat
1800 AND Digmat = @Digmat
1801 AND ISNULL(IdLote,0) = ISNULL(@IdLote,0)
1802
1803 SET @IdoOld = @Ido
1804
1805 FETCH NEXT FROM CURSOR_ATA
1806 INTO @Ido, @Estrut, @Grupo, @Subgrp, @Itemat, @Digmat, @Dt_UltSolicit, @IdLote
1807
1808 END
1809
1810 CLOSE CURSOR_ATA
1811 DEALLOCATE CURSOR_ATA
1812
1813
1814 -------------------------------------------------------------------------------------
1815 -- Atualiza o total do item
1816 UPDATE @TMP_SALDO_REQUISITANTE
1817 SET Vlr_Total = Qtde_Utilizada * Vlr_Unitario
1818 ,Saldo_Financeiro = Qtde_Saldo * Vlr_Unitario
1819
1820 -- Saida da procedure ---------------------------------------------------------
1821 RETURN ;
1822 --SELECT * FROM @TMP_SALDO_REQUISITANTE
1823 -------------------------------------------------------------------------------
1824END