· 4 years ago · May 13, 2021, 01:20 PM
1
2CREATE PROCEDURE dbo.SIMULACAO_PROPOSTA
3 @idCotacao varchar(25),
4 @idCnpj varchar(14),
5 @txMaxima numeric(30,20),
6 @buyerId varchar(40),
7 @tipoCalculo varchar(3),
8 @vlrTAC numeric(30,7),
9 @vlrMargemSimulacao numeric(30,7),
10 @vlrPrincipal decimal(30,4),
11 @prazoSimulacao int,
12 @dtInicioOperacao date,
13 @datPrimParcSimulacao date,
14 @calcularCarencia varchar(3),
15 @analise numeric(10,4),
16 @advalorem numeric(10,4),
17 @bank_slip numeric(10,4),
18 @operacao numeric(10,4),
19 @comissao numeric(10,4),
20 @iof_amount numeric(9,4),
21 @iof_term numeric(9,4)
22AS
23
24BEGIN
25
26 SET NOCOUNT ON;
27
28 declare @carencia int;
29
30 declare @txOperacao numeric(30,20);
31 declare @txDiaria numeric(30,20);
32 declare @txOperacaoComissao numeric(30,20);
33 declare @txOperacaoTOT numeric(30,20);
34 declare @txOperacaoComissaoTOT numeric(30,20);
35
36
37
38 declare @tac numeric(30,7);
39
40 declare @data_primeira_parcela date;
41 declare @tx1 numeric(30,7);
42 declare @vlrAtualizado numeric(30,7);
43 declare @vlrAtualizadoComComissao numeric(30,7);
44
45 declare @vlrFinanciado numeric(30,7);
46 declare @vlrFinanciadoTAC numeric(30,7);
47 declare @vlrCustoAnalise numeric(30,7);
48
49 declare @vlrMargem numeric(16,2);
50 declare @prazoEmprestimo numeric(16,7);
51 declare @pctGarantia numeric(16,7);
52 declare @payment_term numeric(30,7),
53 @vlrIOF_Op numeric(14,7),
54 @vlrIOF_Dias numeric(14,7),
55 @diasTotal_Op int
56 ;
57 -- select @valorCotacao;
58
59
60 -- CALCULA A TAXA MAXIMA
61
62
63 -- CALCULA A TAXA POR DIA
64 SET @txMaxima = @txMaxima / 100;
65 SET @txOperacao = (select power(cast(1 + @txMaxima as numeric(16,10)), cast(1.0000000000/30.0000000000 as numeric(16,10))));
66 SET @txDiaria = @txOperacao;
67 SET @txOperacao = @txOperacao - 1;
68 SET @txOperacaoComissao = (select power(cast(1 + (@txMaxima + (@comissao/100)) as numeric(16,10)), cast(1.0000000000/30.0000000000 as numeric(16,10))));
69 SET @txOperacaoComissao = @txOperacaoComissao - 1;
70
71-- SET @txOperacao = ((@txMaxima + (@comissao/100)) / 30);
72
73 SET @txOperacaoTOT = @txMaxima;
74 SET @txOperacaoComissaoTOT = @txMaxima + (@comissao/100);
75
76
77
78
79
80
81
82
83-- * CARREGA OS DADOS DA TABELA COTACAO OU CAPTURA OS VALORES A SEREM SIMULADOS
84
85 IF @idCotacao IS NULL
86 BEGIN
87 SET @vlrFinanciado = @vlrPrincipal;
88 SET @vlrMargem = @vlrMargemSimulacao;
89 END
90 ELSE
91 BEGIN
92 -- CAPTURA O VALOR DA COTACAO A SER CALCULADO
93 SELECT
94 @vlrFinanciado = valorCotacao,
95 @vlrMargem = margemDisponivel
96 FROM TB_COTACOES tc where idCotacao = @idCotacao;
97
98 END
99
100
101
102
103
104
105
106
107
108
109
110
111-- * TAC
112
113
114 IF @vlrTAC IS NULL
115 BEGIN
116
117 SET @vlrCustoAnalise = ((@analise/100) * @vlrFinanciado);
118
119 IF @vlrCustoAnalise < 50
120 BEGIN
121 SET @vlrCustoAnalise = 50;
122 END
123
124 IF @vlrCustoAnalise > 2615
125 BEGIN
126 SET @vlrCustoAnalise = 2615;
127 END
128
129 SET @tac = @vlrCustoAnalise + ((@advalorem/100 * @vlrFinanciado)) + @bank_slip + @operacao;
130 END
131 ELSE
132 BEGIN
133 SET @tac = @vlrTAC;
134 END
135
136
137 SET @vlrFinanciadoTAC = @vlrFinanciado; -- + @tac;
138
139
140
141
142
143
144
145
146
147
148
149-- * CALCULA O PRAZO MEDIO PONDERADO DA OPERACAO OU UTILIZA O PRAZO DE EMPRESTIMO DO SIMULADOR
150
151
152IF @idCotacao IS NOT NULL
153 BEGIN
154 -- CALCULA O PRAZO MEDIO PONDERADO
155 SET @prazoEmprestimo = (select ROUND(sum(media) / sum(saldoContabil),0) as prazoEmprestimo from (
156 select
157 saldoContabil,
158 DATEDIFF(MONTH, @dtInicioOperacao, dataFinalAtual) as diff,
159 saldoContabil * DATEDIFF(MONTH, @dtInicioOperacao, dataFinalAtual) as media
160 from tb_contratos tc
161 inner join TB_COTACOES_CONTRATOS tcc
162 on tc.nrAnoContrato = tcc.nrAnoContrato
163 where
164 tcc.idCotacao = @idCotacao
165 ) as qr);
166 END
167ELSE
168 SET @prazoEmprestimo = @prazoSimulacao;
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186-- * CALCULA O VALOR ATUALIZADO DA OPERACAO
187
188
189
190 -- PEGA OS DADOS REFERENTES A CARENCIA, DATA DA PRIMEIRA PARCELA E O VP
191
192IF @idCotacao IS NOT NULL
193 BEGIN
194 select
195 @data_primeira_parcela = min(dataPagFuturo),
196 @carencia = min(diff),
197 -- LEVAR O VALOR DE D0 PARA O DIA DA CARENCIA
198 -- TROCAR O NOME DE VP PARA VALOR ATUALIZADO
199 @vlrAtualizado = ((@vlrFinanciadoTAC) * min(diff)) * ((@vlrFinanciadoTAC) * (@txOperacao)) + @vlrFinanciadoTAC,
200 @vlrAtualizadoComComissao = ((@vlrFinanciadoTAC) * min(diff)) * ((@vlrFinanciadoTAC) * (@txOperacaoComissao)) + @vlrFinanciadoTAC
201 -- @vlrAtualizado = ((@vlrFinanciado + @tac) * (@txOperacao) * min(diff)) + @vlrFinanciado,
202 -- @vlrAtualizadoComComissao = ((@vlrFinanciado + @tac) * (@txOperacaoComiss) * min(diff)) + @vlrFinanciado
203 from (select
204 cnpj,
205 dataPagFuturo,
206 DATEDIFF(DAY, @dtInicioOperacao, dataPagFuturo) as diff
207 from tb_contratos_pagamentos tcp
208 where
209 idCotacao = @idCotacao
210 and dataPagFuturo is not null
211 and DATEDIFF(DAY, @dtInicioOperacao, dataPagFuturo) BETWEEN 15 AND 45
212 GROUP BY cnpj, dataPagFuturo) as qr;
213
214 END
215ELSE
216 BEGIN
217 SET @data_primeira_parcela = @datPrimParcSimulacao;
218 IF @calcularCarencia = 'SIM'
219 SET @carencia = DATEDIFF(DAY, @dtInicioOperacao, @data_primeira_parcela);
220 ELSE
221 SET @carencia = 0;
222
223 SET @vlrAtualizado = (@vlrFinanciadoTAC * @txOperacao * @carencia) + @vlrFinanciadoTAC;
224 SET @vlrAtualizadoComComissao = (@vlrFinanciadoTAC * @carencia * @txOperacaoComissao) + @vlrFinanciadoTAC;
225
226 END
227
228
229
230
231
232/*
233 *
234 *
235 * CALCULO DO IOF
236 *
237 *
238 *
239 */
240
241 -- SET @vlrIOF_Op = @vlrAtualizadoComComissao * (@iof_amount/100); (IOF INCIDE SOBRE O PRINCIPAL DE ACORDO COM O BRADESCO)
242 SET @vlrIOF_Op = @vlrPrincipal + @tac * (@iof_amount/100);
243 SET @diasTotal_Op = 0; --DATEDIFF(day, @data_primeira_parcela, DATEADD(month, @prazoEmprestimo, @data_primeira_parcela));
244
245 IF @diasTotal_Op < 365
246 SET @vlrIOF_Dias = @diasTotal_Op * (@iof_term / 100) * @vlrPrincipal + @vlrIOF_Op;
247 ELSE
248 SET @vlrIOF_Dias = 365 * (@iof_term / 100) * @vlrPrincipal + @vlrIOF_Op;
249
250 SET @vlrAtualizadoComComissao = @vlrAtualizadoComComissao + @vlrIOF_Op + @vlrIOF_Dias;
251 SET @vlrAtualizado = @vlrAtualizado + @vlrIOF_Op + @vlrIOF_Dias;
252
253
254/*
255 *
256 *
257 * CALCULO ABERTURA PARCELAS
258 *
259 *
260 *
261 */
262
263
264IF @tipoCalculo = 'SIM'
265 BEGIN
266
267 CREATE TABLE #TMP_SIMULACAO (
268 num_parcela int,
269 dat_parcela date,
270 saldo_sc numeric(30,2),
271 amort_sc numeric(30,2),
272 princ_sc numeric(30,2),
273 juros_sc numeric(30,2),
274 saldo_cc numeric(30,2),
275 amort_cc numeric(30,2),
276 princ_cc numeric(30,2),
277 juros_cc numeric(30,2),
278 comissao numeric(30,2),
279 dias_parcela int,
280 indice_periodo numeric(30,7),
281 indice_ftr_calculo numeric(30,7),
282 iof_parc numeric(30,2),
283 iof_38 numeric(30,2),
284 iof_cheio numeric(30,2)
285 );
286
287
288
289 -- /*
290
291 DECLARE @cnt INT = 0;
292 DECLARE @vlrSaldoSC numeric(20,7) = @vlrPrincipal;
293 DECLARE @vlrSaldoCC numeric(20,7) = @vlrPrincipal;
294 DECLARE @vlrSaldoAntSC numeric(20,7) = 0;
295 DECLARE @vlrSaldoAntCC numeric(20,7) = 0;
296 DECLARE @indicePeriodo numeric(30,20);
297 DECLARE @total_ftr_calculo numeric(30,20);
298
299 DECLARE @dtParcela date;
300 DECLARE @dtParcelaAnt date;
301 DECLARE @addDias int;
302 DECLARE @quantidadeDias int;
303
304 DECLARE @vlrAmortSC numeric(30,7) = @vlrPrincipal;
305 DECLARE @vlrAmortCC numeric(30,7) = @vlrPrincipal;
306
307
308 --DECLARE @vlrPMTSC numeric(14,2) = (select @vlrAtualizado / (power(1 + @txOperacaoTOT, @prazoEmprestimo) - 1) * (@txOperacaoTOT * power(1+ @txOperacaoTOT, @prazoEmprestimo)));
309 DECLARE @vlrPMTSC numeric(30,10) = (select @vlrAtualizado / cast((power(1 + @txOperacaoTOT, @prazoEmprestimo) - 1) as numeric(30,9)) * cast(@txOperacaoTOT * power(1+ @txOperacaoTOT, @prazoEmprestimo) as numeric(30,9)));
310 DECLARE @vlrPMTCC numeric(30,10) = (select @vlrAtualizado / cast((power(1 + @txOperacaoComissaoTOT, @prazoEmprestimo) - 1) as numeric(30,9)) * cast(@txOperacaoComissaoTOT * power(1+ @txOperacaoComissaoTOT, @prazoEmprestimo) as numeric(30,9)));
311
312 -- dbo.PGTO(@txOperacaoComissaoTOT, @prazoEmprestimo, @vlrAtualizadoComComissao);
313
314
315 SET @total_ftr_calculo = 0;
316
317
318 WHILE @cnt <= @prazoEmprestimo
319 BEGIN
320
321 IF @cnt > 0
322
323 BEGIN
324
325 -- DEFINE DATA DA PARCELA BASEADO EM DIAS UTEIS E FERIADOS
326 SET @dtParcela = DATEADD(month, @cnt-1, @data_primeira_parcela);
327 SET @addDias = (CASE DATEPART(WEEKDAY, @dtParcela) WHEN 1 THEN 1 WHEN 7 THEN 2 ELSE 0 END);
328 SET @dtParcela = DATEADD(day, @addDias, @dtParcela);
329 IF EXISTS ( SELECT TOP 1 Nr_Dia FROM dbo.Feriado WITH ( NOLOCK ) WHERE Nr_Dia = DAY(@dtParcela) AND Nr_Mes = MONTH(@dtParcela) AND Tp_Feriado = '1' AND ( Nr_Ano = 0 OR Nr_Ano = YEAR(@dtParcela) ) )
330 SET @dtParcela = DATEADD(day, 1, @dtParcela);
331
332 -- CALCULA O VALOR DA PARCELA
333 SET @vlrSaldoAntSC = @vlrSaldoSC;
334 SET @vlrSaldoAntCC = @vlrSaldoCC;
335 SET @vlrSaldoSC = (@vlrSaldoSC * (1+@txOperacaoTOT)) - @vlrPMTSC;
336 SET @vlrSaldoCC = @vlrSaldoCC * (1+@txOperacaoComissaoTOT) - @vlrPMTCC;
337
338 SET @indicePeriodo = cast(power(@txDiaria, DATEDIFF(DAY, @dtInicioOperacao, @dtParcela)) as numeric(30,9));
339
340 INSERT INTO #TMP_SIMULACAO
341 VALUES
342 (
343 @cnt,
344 @dtParcela, -- DATEADD(month, @cnt-1, @data_primeira_parcela),
345 @vlrSaldoSC,
346 @vlrPMTSC,
347 @vlrSaldoAntSC - @vlrSaldoSC,
348 @vlrPMTSC - (@vlrSaldoAntSC - @vlrSaldoSC),
349 @vlrSaldoCC,
350 @vlrPMTCC,
351 @vlrSaldoAntCC - @vlrSaldoCC,
352 @vlrPMTCC - (@vlrSaldoAntCC - @vlrSaldoCC),
353 (@vlrPMTCC - (@vlrSaldoAntCC - @vlrSaldoCC)) - (@vlrPMTSC - (@vlrSaldoAntSC - @vlrSaldoSC)),
354 DATEDIFF(DAY, @dtInicioOperacao, @dtParcela),
355 @indicePeriodo,
356 (1 / @indicePeriodo),
357 null,
358 null,
359 null
360 );
361
362 SET @total_ftr_calculo = @total_ftr_calculo + (1/@indicePeriodo);
363 END
364
365 SET @cnt = @cnt + 1;
366 END
367
368
369 SET @vlrSaldoSC = @VlrPrincipal + @tac;
370
371 SET @vlrPMTSC = cast((1.0000000/SUM(@total_ftr_calculo)) as numeric(30,7)) * @vlrSaldoSC;
372 SET @cnt = 1;
373
374
375
376 WHILE @cnt <= @prazoEmprestimo
377 BEGIN
378
379 SET @dtParcela = (SELECT dat_parcela FROM #TMP_SIMULACAO WHERE num_parcela = @cnt);
380
381 -- SET @indicePeriodo = (SELECT indice_periodo FROM #TMP_SIMULACAO WHERE num_parcela = @cnt);
382 IF @cnt = 1
383 BEGIN
384 SET @indicePeriodo = cast(power(@txDiaria, DATEDIFF(DAY, @dtInicioOperacao, @dtParcela)) as numeric(30,9));
385 SET @quantidadeDias = DATEDIFF(DAY, @dtInicioOperacao, @dtParcela);
386 END
387 ELSE
388 BEGIN
389 SET @indicePeriodo = cast(power(@txDiaria, DATEDIFF(DAY, @dtParcelaAnt, @dtParcela)) as numeric(30,9));
390 SET @quantidadeDias = DATEDIFF(DAY, @dtInicioOperacao, @dtParcela);
391 IF @quantidadeDias > 365
392 SET @quantidadeDias = 365;
393 END
394
395
396 UPDATE #TMP_SIMULACAO
397 SET
398 saldo_sc = @vlrSaldoSC,
399 amort_sc = @vlrPMTSC,
400 princ_sc = @vlrPMTSC - (@vlrSaldoSC * @indicePeriodo - @vlrSaldoSC),
401 juros_sc = @vlrSaldoSC * @indicePeriodo - @vlrSaldoSC,
402 amort_cc = 0,
403 iof_parc = (@iof_term / 100) * (@vlrPMTSC - (@vlrSaldoSC * @indicePeriodo - @vlrSaldoSC)) * @quantidadeDias,
404 iof_38 = (@iof_amount / 100) * (@vlrPMTSC - (@vlrSaldoSC * @indicePeriodo - @vlrSaldoSC))
405 WHERE
406 num_parcela = @cnt;
407
408
409 SET @dtParcelaAnt = @dtParcela;
410 SET @vlrSaldoAntSC = @vlrSaldoSC;
411 SET @vlrSaldoSC = @vlrSaldoSC - (@vlrPMTSC - (@vlrSaldoSC * @indicePeriodo - @vlrSaldoSC));
412
413 SET @cnt = @cnt + 1;
414
415 END
416
417
418
419 SET @vlrIOF_Dias = (SELECT SUM(iof_parc) FROM #TMP_SIMULACAO);
420 SET @vlrIOF_Op = (SELECT SUM(iof_38) FROM #TMP_SIMULACAO);
421 --SET @vlrIOF_Dias = @vlrIOF_Op;
422
423 SET @vlrIOF_Dias = @vlrIOF_Dias + @vlrIOF_Op;
424
425 SET @vlrAtualizado = (@vlrPrincipal + @tac);
426
427 SET @vlrIOF_Op = (@vlrAtualizado / (@vlrAtualizado - (@vlrIOF_Dias)) * @vlrAtualizado) - @vlrAtualizado;
428 SET @vlrSaldoSC = @vlrAtualizado + @vlrIOF_Op;
429
430 SET @vlrPMTSC = cast((1.0000000/SUM(@total_ftr_calculo)) as numeric(30,7)) * @vlrSaldoSC;
431 SET @cnt = 1;
432
433 WHILE @cnt <= @prazoEmprestimo
434 BEGIN
435
436 SET @dtParcela = (SELECT dat_parcela FROM #TMP_SIMULACAO WHERE num_parcela = @cnt);
437
438 -- SET @indicePeriodo = (SELECT indice_periodo FROM #TMP_SIMULACAO WHERE num_parcela = @cnt);
439 IF @cnt = 1
440 BEGIN
441 SET @indicePeriodo = cast(power(@txDiaria, DATEDIFF(DAY, @dtInicioOperacao, @dtParcela)) as numeric(30,9));
442 SET @quantidadeDias = DATEDIFF(DAY, @dtInicioOperacao, @dtParcela);
443 END
444 ELSE
445 BEGIN
446 SET @indicePeriodo = cast(power(@txDiaria, DATEDIFF(DAY, @dtParcelaAnt, @dtParcela)) as numeric(30,9));
447 SET @quantidadeDias = DATEDIFF(DAY, @dtInicioOperacao, @dtParcela);
448 IF @quantidadeDias > 365
449 SET @quantidadeDias = 365;
450 END
451
452
453 UPDATE #TMP_SIMULACAO
454 SET
455 saldo_cc = @vlrSaldoSC,
456 amort_cc = @vlrPMTSC,
457 princ_cc = @vlrPMTSC - (@vlrSaldoSC * @indicePeriodo - @vlrSaldoSC),
458 juros_cc = @vlrSaldoSC * @indicePeriodo - @vlrSaldoSC,
459 iof_cheio = @vlrIOF_Op
460 WHERE
461 num_parcela = @cnt;
462
463
464 SET @dtParcelaAnt = @dtParcela;
465 SET @vlrSaldoAntSC = @vlrSaldoSC;
466 SET @vlrSaldoSC = @vlrSaldoSC - (@vlrPMTSC - (@vlrSaldoSC * @indicePeriodo - @vlrSaldoSC));
467
468 SET @cnt = @cnt + 1;
469
470 END
471
472 SET @vlrIOF_Dias = (SELECT SUM(iof_38) FROM #TMP_SIMULACAO);
473
474
475-- UPDATE #TMP_SIMULACAO SET iof_cheio = @vlrIOF_Dias;
476
477 SELECT * FROM #TMP_SIMULACAO ORDER BY num_parcela ASC;
478 DROP TABLE #TMP_SIMULACAO;
479
480 END
481ELSE
482 BEGIN
483 SELECT
484 CAST(@comissao AS NUMERIC(4,4)) as TX_COMISSAO,
485 @iof_amount AS IOF_AMOUNT,
486 cast(@vlrIOF_Op as numeric(12,2)) as VLR_IOF_OPERACAO,
487 @iof_term AS IOF_TERM,
488 cast(@vlrIOF_Dias as numeric(12,2)) as VLR_IOF_TERM,
489 @data_primeira_parcela AS PRIM_PARC,
490 @diasTotal_Op AS TOTAL_DIAS_PARCELAS,
491 CAST(ROUND(@prazoEmprestimo,0) AS INT) AS PRAZO_EMPRESTIMO,
492 CAST(@txOperacaoTOT AS NUMERIC(12,6)) AS TX_MAXIMA,
493 CAST(@txOperacaoComissaoTOT AS NUMERIC(12,6)) AS TX_MAXIMA_COMISSAO,
494 CAST(@txOperacao AS NUMERIC(12,9)) AS TX_AO_DIA,
495 CAST(@vlrFinanciado AS NUMERIC(12,3)) AS VLR_COTACAO,
496 CAST(@vlrMargem AS NUMERIC(12,3)) AS VLR_MARGEM,
497 CAST(@vlrAtualizado AS NUMERIC(12,3)) AS VLR_VP,
498 CAST(@vlrAtualizadoComComissao AS NUMERIC(12,3)) AS VLR_VP10,
499 CAST(@carencia AS INT) AS DIAS_CARENCIA,
500 CAST(dbo.PGTO(@txOperacaoTOT, @prazoEmprestimo, @vlrAtualizado) AS NUMERIC(12,3)) as PMT_SEM_COMMISSAO,
501 CAST(dbo.PGTO(@txOperacaoComissaoTOT, @prazoEmprestimo, @vlrAtualizadoComComissao) AS NUMERIC(12,3)) as PMT_COM_COMMISSAO,
502 CAST(ROUND(dbo.PGTO(@txOperacaoComissaoTOT, @prazoEmprestimo, @vlrAtualizadoComComissao) * round(@prazoEmprestimo,0), 3) AS NUMERIC(12,3)) AS TOTAL_EMPRESTIMO, -- SOMATORIA DE PARCELAS
503 CAST((dbo.FV(@txOperacaoComissaoTOT/30, @carencia, 0, @vlrFinanciado,0)*-1) AS NUMERIC(12,3)) AS VF,
504 CAST(ROUND(((dbo.PGTO(@txOperacaoComissaoTOT, round(@prazoEmprestimo,0), @vlrAtualizado) * round(@prazoEmprestimo,0)) / @vlrMargem) * 100, 3) AS NUMERIC(12,3)) as CALCULO,
505 CAST(round(@vlrFinanciado / @vlrMargem, 4) AS NUMERIC(12,3)) as relacao
506 ;
507
508 END
509
510END;
511go
512
513