· 8 years ago · Jul 30, 2018, 08:12 PM
1--liquibase formatted sql
2
3-- Ticket : SRP-728
4-- Descricao : Modificar estrutura interna do sistema para permitir o controle de saldo fiscal e pendência (reserva) por Lote / NF Cobertura
5-- Autor : lidiane.parreira
6-- Data : 04/08/2017 14:30:00
7-- Source : {8.9.4.0}
8
9--changeset lidiane.parreira:SRP-728_0 logicalFilePath:2428
10--comment: Clonando a tabela COBERTURALOTE
11create table COBERTURALOTE_TEMP tablespace WMS_DADOSMOV as select * from COBERTURALOTE;
12--rollback drop table COBERTURALOTE_TEMP;
13
14--changeset lidiane.parreira:SRP-728_1 logicalFilePath:2428
15--comment: Criando coluna QTDECONSUMIDA na nova tabela COBERTURALOTE_TEMP
16alter table COBERTURALOTE_TEMP add QTDECONSUMIDA number default 0;
17--rollback alter table COBERTURALOTE_TEMP drop column QTDECONSUMIDA;
18
19--changeset lidiane.parreira:SRP-728_2 logicalFilePath:2428
20--comment: Criando Ãndice para tabela COBERTURALOTE_TEMP
21create index IDX_COBERTURALOTE_TEMP on COBERTURALOTE_TEMP(IDLOTE, IDNOTAFISCAL, IDNFDET) tablespace WMS_INDICEMOV;
22--rollback drop index IDX_COBERTURALOTE_TEMP;
23
24--changeset lidiane.parreira:SRP-728_3 logicalFilePath:2428
25--comment: Criando Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
26create index IDX_COBERTURALOTE_QTD_DISP on COBERTURALOTE(IDLOTE, (QTDECOBERTO - QTDERETORNO)) tablespace WMS_INDICEMOV;
27--rollback drop index IDX_COBERTURALOTE_QTD_DISP;
28
29--changeset lidiane.parreira:SRP-728_4 logicalFilePath:2428
30--comment: Criando Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
31create index IDX_COMPOSICAOLOTE_QTD_DISP on COMPOSICAOLOTE(IDLOTENOVO, (QTDECOBERTA - QTDERETORNO)) tablespace WMS_INDICEMOV;
32--rollback drop index IDX_COMPOSICAOLOTE_QTD_DISP;
33
34--changeset lidiane.parreira:SRP-728_5 logicalFilePath:2428
35--comment: Criando Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
36create index IDX_NOTAFISCAL_NF_STATUS on NOTAFISCAL(IDNOTAFISCAL, decode(trim(STATUSNF), 'P', 0, 1)) tablespace WMS_INDICEMOV;
37--rollback drop index IDX_NOTAFISCAL_NF_STATUS;
38
39--changeset lidiane.parreira:SRP-728_6 logicalFilePath:2428
40--comment: Criando Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
41create index IDX_MAPAALOCACAO_LOTE_FIN on MAPAALOCACAO(IDLOTE, decode(trim(STATUS), 'F', 1, 0)) tablespace WMS_INDICEMOV;
42--rollback drop index IDX_MAPAALOCACAO_LOTE_FIN;
43
44--changeset lidiane.parreira:SRP-728_7 logicalFilePath:2428
45--comment: Criando Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
46create index IDX_COBERTURALOTE_TEMP_COB on COBERTURALOTE_TEMP ((nvl(QTDECOBERTO, 0) - nvl(QTDERETORNO, 0) - nvl(QTDECONSUMIDA, 0)), IDLOTE) tablespace WMS_INDICEMOV;
47--rollback drop index IDX_COBERTURALOTE_TEMP_COB;
48
49--changeset lidiane.parreira:SRP-728_8 logicalFilePath:2428
50--comment: Criando Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
51create index IDX_COBNOTAFISCAL_IDLOTE on COBERTURANOTAFISCAL(IDLOTEPSNF) tablespace WMS_INDICEMOV;
52--rollback drop index IDX_COBNOTAFISCAL_IDLOTE;
53
54--changeset lidiane.parreira:SRP-728_9 logicalFilePath:2428
55--comment: Criando Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
56create index IDX_RETORNOSIMBOLICO_VD_RS on RETORNOSIMBOLICO(IDNOTAFISCALVENDA, IDNOTAFISCALRETORNO) tablespace WMS_INDICEMOV;
57--rollback drop index IDX_RETORNOSIMBOLICO_VD_RS;
58
59--changeset lidiane.parreira:SRP-728_10 endDelimiter:/ logicalFilePath:2428
60--comment: Alimentando a nova tabela COBERTURALOTEDISPNF
61declare
62 type t_cursor is ref cursor;
63
64 c_cursorCobertura t_cursor;
65 r_coberturalote coberturalote_temp%rowtype;
66 v_qtdeDisponivelConsumir number := 0;
67 v_qtdeRetornoOndaNaoProcessada number := 0;
68 v_qtdeUtilizada number := 0;
69 v_qtdeAnterior number := 0;
70 v_count number := 0;
71 v_msgerro varchar2(4000);
72
73 procedure retornarCoberturaLote
74 (
75 p_idlote in number,
76 p_cursor in out t_cursor,
77 p_coberturalote in out coberturalote_temp%rowtype
78 ) is
79 v_indice number := 1;
80
81 function retornarSqlCobertura
82 (
83 p_indice in number,
84 p_idlote in number
85 ) return varchar2 is
86 v_sql varchar2(5000);
87 v_sql_complemento varchar2(5000);
88 begin
89
90 v_sql := 'select cl.idlotenf, cl.idlote, cl.idnotafiscal, ' ||
91 ' nvl(cl.qtdecoberto, 0) qtdecoberto, ' ||
92 ' nvl(cl.qtderetorno, 0) qtderetorno, ' ||
93 ' cl.idnfentrada, cl.idnfdet, cl.idnfdetentrada, ' ||
94 ' nvl(cl.qtdeconsumida, 0) qtdeconsumida ' ||
95 ' from coberturalote_temp cl ' ||
96 ' where (nvl(cl.qtdecoberto, 0) - nvl(cl.qtderetorno, 0) - nvl(cl.qtdeconsumida, 0)) > 0 ';
97
98 if p_indice = 1 then
99 v_sql_complemento := ' and cl.idlote = ' || p_idlote;
100 else
101 for i in reverse 2 .. p_indice
102 loop
103 if i = p_indice then
104 v_sql_complemento := '(select c.idloteanterior from composicaolote c where (nvl(c.qtdecoberta, 0) - nvl(c.qtderetorno, 0) > 0) and c.idlotenovo = ' ||
105 p_idlote || ')';
106 else
107 v_sql_complemento := '(select c.idloteanterior from composicaolote c where (nvl(c.qtdecoberta, 0) - nvl(c.qtderetorno, 0) > 0) and c.idlotenovo in ' ||
108 v_sql_complemento || ')';
109 end if;
110 end loop;
111
112 v_sql_complemento := ' and cl.idlote in (' || v_sql_complemento || ')';
113 end if;
114
115 v_sql := v_sql || v_sql_complemento;
116
117 return v_sql;
118 end retornarSqlCobertura;
119
120 function retornarSqlComposicao
121 (
122 p_indice in number,
123 p_idlote in number
124 ) return varchar2 is
125 v_sql varchar2(5000);
126 v_sql_complemento varchar2(5000);
127 begin
128 v_sql := 'select count(c.idlotenovo) total' ||
129 ' from composicaolote c, lote cl' ||
130 ' where cl.idlote = c.idloteanterior' ||
131 ' and (nvl(c.qtdecoberta,0) - nvl(c.qtderetorno,0) > 0)' ||
132 ' and c.idloteanterior <> c.idlotenovo';
133
134 if p_indice = 1 then
135 v_sql_complemento := ' and cl.idlote = ' || p_idlote;
136 else
137 for i in reverse 2 .. p_indice
138 loop
139 if i = p_indice then
140 v_sql_complemento := '(select c.idloteanterior from composicaolote c where (nvl(c.qtdecoberta, 0) - nvl(c.qtderetorno, 0)) > 0 and c.idlotenovo = ' ||
141 p_idlote || ')';
142 else
143 v_sql_complemento := '(select c.idloteanterior from composicaolote c where (nvl(c.qtdecoberta, 0) - nvl(c.qtderetorno, 0)) > 0 and c.idlotenovo in ' ||
144 v_sql_complemento || ')';
145 end if;
146 end loop;
147
148 v_sql_complemento := ' and cl.idlote in (' || v_sql_complemento || ')';
149 end if;
150
151 v_sql := v_sql || v_sql_complemento;
152
153 return v_sql;
154 end retornarSqlComposicao;
155
156 procedure retornarCoberturaLoteDisp
157 (
158 p_indice in out number,
159 p_idlote in number,
160 p_cursorCob in out t_cursor
161 ) is
162 v_sql varchar2(5000);
163 v_composicao number;
164 begin
165 if (p_cursorCob%isopen) then
166 close p_cursorCob;
167 end if;
168
169 v_sql := retornarSqlCobertura(p_indice, p_idlote);
170 open p_cursorCob for v_sql;
171
172 fetch p_cursorCob
173 into r_coberturalote;
174
175 if (p_cursorCob%notfound) then
176 raise no_data_found;
177 end if;
178
179 p_coberturalote := r_coberturalote;
180 exception
181 when no_data_found then
182 p_indice := p_indice + 1;
183 v_sql := retornarSqlComposicao(p_indice, p_idlote);
184
185 execute immediate v_sql
186 into v_composicao;
187
188 if v_composicao > 0 then
189 retornarCoberturaLoteDisp(p_indice, p_idlote, p_cursorCob);
190 else
191 r_coberturalote.idlotenf := null;
192 r_coberturalote.idlote := null;
193 r_coberturalote.idnotafiscal := null;
194 r_coberturalote.idnfentrada := null;
195 r_coberturalote.qtdecoberto := null;
196 r_coberturalote.qtderetorno := null;
197 end if;
198 end retornarCoberturaLoteDisp;
199
200 begin
201 retornarCoberturaLoteDisp(v_indice, p_idlote, p_cursor);
202 end retornarCoberturaLote;
203
204begin
205 delete from coberturalotedispnf;
206
207 for c_lote in (select l.idlote, l.qtdedisponivel qtdeDisponivelLote
208 from lote l, depositante d, regime r
209 where l.iddepositante = d.identidade
210 and d.idregime = r.idregime
211 and r.classificacao = 'A'
212 and exists (select 1
213 from lotelocal ll
214 where ll.idlote = l.idlote
215 and ll.estoque > 0
216 union
217 select 1
218 from mapaalocacao m
219 where decode(trim(m.status), 'F', 1, 0) = 0
220 and m.idlote = l.idpalet
221 union
222 select 1
223 from mapaalocacao m, lote lt
224 where m.idlote = lt.idpalet
225 and decode(trim(m.status), 'F', 1, 0) = 0
226 and lt.idlote = l.idlote)
227 and exists (select 1
228 from coberturalote cl
229 where cl.idlote = l.idlote
230 and (cl.qtdecoberto - cl.qtderetorno) > 0
231 union
232 select 1
233 from composicaolote co
234 where co.idlotenovo = l.idlote
235 and (co.qtdecoberta - co.qtderetorno) > 0)
236 and not exists
237 (select oo.idlote_origem, oo.qtde, oo.idlote,
238 oo.idlotenf, os.*
239 from orlote_origem oo, lote lo, ordemservico os
240 where oo.idlote = l.idlote
241 and oo.idlote_origem = lo.idlote
242 and os.idlotenf = oo.idlotenf
243 and os.tiposervico in ('K', 'D')
244 and os.situacao in ('P')))
245 loop
246 -- deduzir do estoque disponÃvel do lote, a quantidade do mesmo
247 -- que foi utilizada em Retorno de Armazenagem ou Retorno Simbólico,
248 -- cuja NF ainda esteja pendente de processamento
249 select sum(cb.qtde)
250 into v_qtdeRetornoOndaNaoProcessada
251 from coberturanotafiscal cb
252 where cb.idlotepsnf = c_lote.idlote
253 and exists
254 (
255 -- notas de retorno de armazenagem ainda não processadas
256 select 1
257 from retornosimbolico rs, nfromaneio nfr, notafiscal nf
258 where nfr.idnotafiscal = rs.idnotafiscalretorno
259 and nf.idnotafiscal = nfr.idnotafiscal
260 and decode(trim(nf.statusnf), 'P', 0, 1) = 1
261 and rs.idnotafiscalretorno = cb.idnotafiscalretorno
262 union
263 -- retornos simbólicos ainda não processados
264 select 1
265 from retornosimbolico rs, nfromaneio nfr, notafiscal nf
266 where nfr.idnotafiscal = rs.idnotafiscalvenda
267 and nf.idnotafiscal = nfr.idnotafiscal
268 and decode(trim(nf.statusnf), 'P', 0, 1) = 1
269 and rs.idnotafiscalretorno = cb.idnotafiscalretorno);
270
271 if (v_qtdeRetornoOndaNaoProcessada > 0) then
272 c_lote.qtdedisponivellote := c_lote.qtdedisponivellote -
273 v_qtdeRetornoOndaNaoProcessada;
274 end if;
275
276 v_qtdeUtilizada := 0;
277
278 -- para cada lote, percorrer a estrutura de cobertura fiscal até que
279 -- toda quantidade disponÃvel do lote tenha sido contemplada com as devidas
280 -- NF de Cobertura
281 while (c_lote.qtdedisponivellote > 0)
282 loop
283 v_qtdeAnterior := c_lote.qtdedisponivellote;
284
285 retornarCoberturaLote(c_lote.idlote, c_cursorCobertura,
286 r_coberturalote);
287
288 v_qtdeDisponivelConsumir := r_coberturalote.qtdecoberto -
289 r_coberturalote.qtderetorno -
290 r_coberturalote.qtdeconsumida;
291
292 if (v_qtdeDisponivelConsumir is null) then
293 raise_application_error(-20000,
294 'Não foi encontrada cobertura para o lote id: ' ||
295 c_lote.idlote);
296 end if;
297
298 if (v_qtdeDisponivelConsumir > 0) then
299 if (v_qtdeDisponivelConsumir >= c_lote.qtdedisponivellote) then
300 v_qtdeUtilizada := c_lote.qtdedisponivellote;
301 else
302 v_qtdeUtilizada := v_qtdeDisponivelConsumir;
303 end if;
304
305 -- atualizar a quantidade consumida da estrutura de apoio coberturalote_temp
306 -- para evitar que seja utilizada incorretamente a quantidade do lote de cobertura
307 update coberturalote_temp cl
308 set cl.qtdeconsumida = cl.qtdeconsumida + v_qtdeUtilizada
309 where cl.idlote = r_coberturalote.idlote
310 and cl.idnotafiscal = r_coberturalote.idnotafiscal
311 and cl.idnfdet = r_coberturalote.idnfdet
312 and cl.idnfentrada = r_coberturalote.idnfentrada
313 and cl.idnfdetentrada = r_coberturalote.idnfdetentrada;
314 begin
315 insert into coberturalotedispnf
316 (id, idlote, idlotecobertura, qtdedisponivel, qtdereservado,
317 idnotafiscalcobertura, idnfdetcobertura, idlotenf, idnfentrada,
318 idnfdetentrada)
319 values
320 (seq_coberturalotedispnf.nextval, c_lote.idlote,
321 r_coberturalote.idlote, v_qtdeUtilizada, 0,
322 r_coberturalote.idnotafiscal, r_coberturalote.idnfdet,
323 r_coberturalote.idlotenf, r_coberturalote.idnfentrada,
324 r_coberturalote.idnfdetentrada);
325 exception
326 when dup_val_on_index then
327 update coberturalotedispnf
328 set qtdedisponivel = qtdedisponivel + v_qtdeUtilizada
329 where idlote = c_lote.idlote
330 and idlotecobertura = r_coberturalote.idlote
331 and idnotafiscalcobertura = r_coberturalote.idnotafiscal
332 and idnfdetcobertura = r_coberturalote.idnfdet
333 and idnfentrada = r_coberturalote.idnfentrada
334 and idnfdetentrada = r_coberturalote.idnfdetentrada;
335 end;
336
337 c_lote.qtdedisponivellote := c_lote.qtdedisponivellote -
338 v_qtdeUtilizada;
339 end if;
340
341 if (c_cursorCobertura%isopen) then
342 close c_cursorCobertura;
343 end if;
344
345 if (v_qtdeAnterior = c_lote.qtdedisponivellote) then
346 raise_application_error(-20000,
347 'Não foi deduzida a quantidade disponÃvel do lote id: ' ||
348 c_lote.idlote);
349 end if;
350 end loop;
351
352 v_count := v_count + 1;
353
354 if (mod(v_count, 200) = 0) then
355 commit;
356 end if;
357 end loop;
358
359 commit;
360end;
361/
362--rollback delete from COBERTURALOTEDISPNF;
363
364--changeset lidiane.parreira:SRP-728_11 logicalFilePath:2428
365--comment: Drop na tabela COBERTURALOTE_TEMP
366drop table COBERTURALOTE_TEMP;
367--rollback not required.
368
369--changeset lidiane.parreira:SRP-728_12 logicalFilePath:2428
370--comment: Removendo Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
371drop index IDX_COBERTURALOTE_QTD_DISP;
372--rollback not required.
373
374--changeset lidiane.parreira:SRP-728_13 logicalFilePath:2428
375--comment: Removendo Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
376drop index IDX_COMPOSICAOLOTE_QTD_DISP;
377--rollback not required.
378
379--changeset lidiane.parreira:SRP-728_14 logicalFilePath:2428
380--comment: Removendo Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
381drop index IDX_NOTAFISCAL_NF_STATUS;
382--rollback not required.
383
384--changeset lidiane.parreira:SRP-728_15 logicalFilePath:2428
385--comment: Removendo Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
386drop index IDX_MAPAALOCACAO_LOTE_FIN;
387--rollback not required.
388
389--changeset lidiane.parreira:SRP-728_16 logicalFilePath:2428
390--comment: Removendo Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
391drop index IDX_COBNOTAFISCAL_IDLOTE;
392--rollback not required.
393
394--changeset lidiane.parreira:SRP-728_17 logicalFilePath:2428
395--comment: Removendo Ãndice temporário para atualização da tabela COBERTURALOTEDISPNF
396drop index IDX_RETORNOSIMBOLICO_VD_RS;
397--rollback not required.