· 4 years ago · May 26, 2021, 04:44 PM
1/****** Object: StoredProcedure [dbo].[Hermes_Captura30CSNA] Script Date: 26/05/2021 17:33:12 ******/
2SET ANSI_NULLS ON
3GO
4SET QUOTED_IDENTIFIER ON
5GO
6ALTER PROCEDURE [dbo].[Hermes_Captura30CSNA] (
7 @file VARCHAR(2000), -- = \\Ssmadtcweb83\interfases\int\Ficheros\ALGO_DETTM_UCC\input\captura30_CSNA.ide
8 @operativa VARCHAR(1000) -- = CSNA (siempre)
9)
10AS
11BEGIN
12
13 DECLARE @fechaHoy DATETIME
14 SET @fechaHoy = CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 121) + ' 00:00:00.000');
15 DECLARE @TEMP_TABLE_NAME NVARCHAR(250);
16 SET @TEMP_TABLE_NAME = 'dbo.[IFA_TEMP_'+format(cast(getdate() as date), 'yyMMdd')+'_' + REPLACE(@operativa,'\','_') + '_' + convert(varchar(max),abs(checksum(NEWID())))+']'
17 DECLARE @sql varchar(MAX);
18 SET @sql = 'DROP TABLE IF EXISTS ' +@TEMP_TABLE_NAME+
19 ' CREATE TABLE '+@TEMP_TABLE_NAME+'([campo1] [varchar](2000) NULL,
20 [campo2] [varchar](2000) NULL, [campo3] [varchar](2000) NULL, [campo4] [varchar](2000) NULL,
21 [campo5] [varchar](2000) NULL, [campo6] [varchar](2000) NULL, [campo7] [VARCHAR](2000) NULL,
22 [campo8] [varchar](2000) NULL, [campo9] [varchar](2000) NULL, [campo10] [varchar](2000) NULL,
23 [campo11] [varchar](2000) NULL, [campo12] [varchar](2000) NULL, [campo13] [varchar](2000) NULL,
24 [campo14] [varchar](2000) NULL, [campo15] [varchar](2000) NULL, [campo16] [varchar](2000) NULL,
25 [campo17] [varchar](2000) NULL, [campo18] [varchar](2000) NULL, [campo19] [varchar](2000) NULL,
26 [campo20] [varchar](2000) NULL, [campo21] [varchar](2000) NULL, [campo22] [varchar](2000) NULL,
27 [campo23] [varchar](2000) NULL, [campo24] [varchar](2000) NULL, [campo25] [varchar](2000) NULL,
28 [campo26] [varchar](2000) NULL, [campo27] [varchar](2000) NULL, [campo28] [varchar](2000) NULL,
29 [campo29] [varchar](2000) NULL, [campo30] [varchar](2000) NULL, [campo31] [varchar](2000) NULL,
30 [campo32] [varchar](2000) NULL, [campo33] [varchar](2000) NULL, [campo34] [varchar](2000) NULL,
31 [campo35] [varchar](2000) NULL, [campo36] [varchar](2000) NULL, [campo37] [varchar](2000) NULL,
32 [campo38] [varchar](2000) NULL, [campo39] [varchar](2000) NULL, [campo40] [varchar](2000) NULL,
33 [campo41] [varchar](2000) NULL, [campo42] [varchar](2000) NULL, [campo43] [varchar](2000) NULL,
34 [campo44] [varchar](2000) NULL, [campo45] [varchar](2000) NULL, [campo46] [varchar](2000) NULL,
35 [campo47] [varchar](2000) NULL, [campo48] [varchar](2000) NULL, [campo49] [varchar](2000) NULL,
36 [campo50] [varchar](2000) NULL, [campo51] [varchar](2000) NULL, [campo52] [varchar](2000) NULL,
37 [campo53] [varchar](2000) NULL, [campo54] [varchar](2000) NULL, [campo55] [varchar](2000) NULL,
38 [campo56] [varchar](2000) NULL) ON [PRIMARY]'
39
40 EXEC(@sql)
41
42 CREATE TABLE #DataTable ([campo1] [varchar](2000) NULL,
43 [campo2] [varchar](2000) NULL, [campo3] [varchar](2000) NULL, [campo4] [varchar](2000) NULL,
44 [campo5] [varchar](2000) NULL, [campo6] [varchar](2000) NULL, [campo7] [VARCHAR](2000) NULL,
45 [campo8] [varchar](2000) NULL, [campo9] [varchar](2000) NULL, [campo10] [varchar](2000) NULL,
46 [campo11] [varchar](2000) NULL, [campo12] [varchar](2000) NULL, [campo13] [varchar](2000) NULL,
47 [campo14] [varchar](2000) NULL, [campo15] [varchar](2000) NULL, [campo16] [varchar](2000) NULL,
48 [campo17] [varchar](2000) NULL, [campo18] [varchar](2000) NULL, [campo19] [varchar](2000) NULL,
49 [campo20] [varchar](2000) NULL, [campo21] [varchar](2000) NULL, [campo22] [varchar](2000) NULL,
50 [campo23] [varchar](2000) NULL, [campo24] [varchar](2000) NULL, [campo25] [varchar](2000) NULL,
51 [campo26] [varchar](2000) NULL, [campo27] [varchar](2000) NULL, [campo28] [varchar](2000) NULL,
52 [campo29] [varchar](2000) NULL, [campo30] [varchar](2000) NULL, [campo31] [varchar](2000) NULL,
53 [campo32] [varchar](2000) NULL, [campo33] [varchar](2000) NULL, [campo34] [varchar](2000) NULL,
54 [campo35] [varchar](2000) NULL, [campo36] [varchar](2000) NULL, [campo37] [varchar](2000) NULL,
55 [campo38] [varchar](2000) NULL, [campo39] [varchar](2000) NULL, [campo40] [varchar](2000) NULL,
56 [campo41] [varchar](2000) NULL, [campo42] [varchar](2000) NULL, [campo43] [varchar](2000) NULL,
57 [campo44] [varchar](2000) NULL, [campo45] [varchar](2000) NULL, [campo46] [varchar](2000) NULL,
58 [campo47] [varchar](2000) NULL, [campo48] [varchar](2000) NULL, [campo49] [varchar](2000) NULL,
59 [campo50] [varchar](2000) NULL, [campo51] [varchar](2000) NULL, [campo52] [varchar](2000) NULL,
60 [campo53] [varchar](2000) NULL, [campo54] [varchar](2000) NULL, [campo55] [varchar](2000) NULL,
61 [campo56] [varchar](2000) NULL) ON [PRIMARY]
62 CREATE TABLE #DataTable2 ([campo1] [varchar](2000) NULL) ON [PRIMARY]
63 CREATE TABLE #IdSubyacenteMercadoTable ([IdSubyacenteMercado] int) ON [PRIMARY]
64
65 BEGIN TRY
66 BEGIN TRY
67 DECLARE @varbulk varchar(2000)
68 SET @varbulk = 'bulk insert '+@TEMP_TABLE_NAME+' from ''' + @file +
69 ''' with (DATA_SOURCE = ''hermesdatasourcemsi'', FIELDTERMINATOR = '','', KEEPNULLS, ROWTERMINATOR = ''\n'')'
70 EXEC(@varbulk)
71 -- volcado de datos a la tabla temporal #DataTable y eliminacion de la tabla @TEMP_TABLE_NAME
72 SET @sql = 'INSERT INTO #DataTable SELECT * FROM ' + @TEMP_TABLE_NAME +
73 ' DROP TABLE ' + @TEMP_TABLE_NAME
74 EXEC(@sql)
75 -- #DataTable2 contiene todos los subyref que aparecen en el fichero @file en el campo30
76 INSERT INTO #DataTable2
77 SELECT DISTINCT SUBSTRING(campo30, CHARINDEX('CI#FwdC_', campo30) + LEN('CI#FwdC_'), LEN(campo30)) FROM #DataTable
78
79 -- #IdSubyacenteMercadoTable contiene todos los IdSubyacenteMercado relacionados con los SubyRef de #DataTable2
80 INSERT INTO #IdSubyacenteMercadoTable
81 SELECT IdSubyacenteMercado FROM SubyacenteMercado
82 INNER JOIN #DataTable2 dt2 ON SubyacenteMercado.SubyRef = dt2.campo1
83 WHERE CodClaseDato = 2 -- Commodity
84 AND Situacion = 1 -- Activo
85 AND SubyRef is not null AND SubyRef <> ''
86 ORDER BY plazo
87
88 -- Crear AlertaCSNA, si no se ha creado ya, si existe alguna línea en el fichero donde el campo10 (SubyRef) venga vacío
89 IF (((SELECT COUNT(*) FROM SubyacenteMercado
90 INNER JOIN #DataTable2 dt2 ON SubyacenteMercado.SubyRef = dt2.campo1
91 WHERE CodClaseDato = 2 -- Commodity
92 AND Situacion = 1 -- Activo
93 AND (SubyRef is null OR SubyRef = '')) > 0)
94 AND ((SELECT COUNT(*) FROM AlertaCSNA WHERE SubyRef = '' AND CodAllegro = '' AND FechaRegistro >= CONVERT(date, GETDATE())) = 0))
95 BEGIN
96 -- Todo vacío para no interferir con el resto de alarmas
97 INSERT INTO AlertaCSNA VALUES (CONVERT(DATE, GETDATE()), '', '', NULL, NULL, NULL, NULL, NULL, NULL)
98 END
99
100 -- Se eliminan las posiciones vivas de hace más de 30 días (una posición tiene una vida de 30 días).
101 DELETE FROM MarcadorPosViva_All WHERE DATEDIFF(d, FechaRegistro, GETDATE()) > 30;
102
103 -- 0. Insercion en MarcadorPosViva_Desconocidos de los codigos de SubyRef de #DataTable2 que nunca antes
104 -- hayan aparecido como SubyRef en algun subyacente de la tabla SubyacenteMercado
105 BEGIN TRY
106 INSERT INTO MarcadorPosViva_Desconocidos ([UUM], [CodSubyacente], [SubyRef], [FechaRechazo], [Fichero], [DETTM], [UC], [STC], [CSNA])
107 SELECT 'Desconocidos', campo1 CodSubyacente, NULL, GETDATE(), @file, 0, 0, 0, 1 CSNA
108 FROM (SELECT campo1 FROM #DataTable2 EXCEPT SELECT DISTINCT SubyRef FROM SubyacenteMercado) AS T
109 END TRY
110 BEGIN CATCH
111 INSERT INTO Actividad (UUM, FechaHora, Fichero, Previsualizacion, Resultado, SistemaDestino, SistemaOrigen, TipoActividad, Visualizacion, IdUsuario)
112 VALUES ('DL_REPSOL\PROCESOS', GETDATE(), @file, 'Hermes_Captura30CSNA', 'Error insertando Desconocidos ' + @operativa, 'HERMES', @operativa, 'MarcadorPosViva_Desconocidos',
113 ERROR_MESSAGE(), 59);
114 END CATCH
115
116 BEGIN TRY
117 -- Se insertan en MarcadorPosViva los subyacentes para los que nunca se haya capturado posición, activándose el bit "ExtraccionCSNA"
118 INSERT INTO MarcadorPosViva
119 SELECT sm.IdSubyacenteMercado, @fechaHoy, @fechaHoy, 0, 0, 0, 1 ExtraccionCSNA, 0, @file, GETDATE(), 0, 0, NULL
120 FROM #IdSubyacenteMercadoTable sm
121 LEFT OUTER JOIN MarcadorPosViva mpv ON sm.IdSubyacenteMercado = mpv.IdSubyacenteMercado
122 WHERE mpv.IdSubyacenteMercado IS NULL;
123
124 UPDATE MarcadorPosViva
125 SET FLETES = NULL, RYTTSA = NULL, CantidadPosicion = NULL
126 WHERE FechaUltimaPosicion < CONVERT(date, GETDATE())
127 AND IdSubyacenteMercado in (SELECT sm.IdSubyacenteMercado
128 FROM #IdSubyacenteMercadoTable sm
129 LEFT OUTER JOIN MarcadorPosViva mpv ON sm.IdSubyacenteMercado = mpv.IdSubyacenteMercado
130 WHERE mpv.IdSubyacenteMercado IS NOT NULL)
131
132 -- Se actualizan en MarcadorPosViva los subyacentes para los que sí se ha capturado posición en algún momento:
133 -- Se desactivan los bits de extracción para los que no se haya recibido posición en los últimos 30 días (MarcadorPosViva_All)
134 -- Se activa el bit "ExtraccionCSNA"
135 -- Se actualiza FechaUltimaPosicion = HOY
136 DECLARE @idsubyacente INT
137 DECLARE cur_subyacentesPosViva CURSOR FOR
138 SELECT sm.IdSubyacenteMercado
139 FROM #IdSubyacenteMercadoTable sm
140 LEFT OUTER JOIN MarcadorPosViva mpv ON sm.IdSubyacenteMercado = mpv.IdSubyacenteMercado
141 WHERE mpv.IdSubyacenteMercado IS NOT NULL
142
143 OPEN cur_subyacentesPosViva
144 FETCH NEXT FROM cur_subyacentesPosViva
145 INTO @idsubyacente
146
147 WHILE @@FETCH_STATUS = 0
148 BEGIN
149 UPDATE MarcadorPosViva
150 SET FechaUltimaPosicion = @fechaHoy,
151 ExtraccionCSNA = 1,
152 FechaOrigenProcedimiento = GETDATE(),
153 FicheroOrigen = @file,
154 ExtraccionDETTM = CASE WHEN 1 = SOME (SELECT ExtraccionDETTM FROM MarcadorPosViva_All
155 WHERE IdSubyacenteMercado = @idsubyacente and ExtraccionDETTM = 1) THEN 1 ELSE 0 END,
156 ExtraccionUCC = CASE WHEN 1 = SOME (SELECT ExtraccionUCC FROM MarcadorPosViva_All
157 WHERE IdSubyacenteMercado = @idsubyacente and ExtraccionUCC = 1) THEN 1 ELSE 0 END,
158 ExtraccionSTC = CASE WHEN 1 = SOME (SELECT ExtraccionSTC FROM MarcadorPosViva_All
159 WHERE IdSubyacenteMercado = @idsubyacente and ExtraccionSTC = 1) THEN 1 ELSE 0 END,
160 ExtraccionRMF = CASE WHEN 1 = SOME (SELECT ExtraccionRMF FROM MarcadorPosViva_All
161 WHERE IdSubyacenteMercado = @idsubyacente and ExtraccionRMF = 1) THEN 1 ELSE 0 END
162 WHERE MarcadorPosViva.IdSubyacenteMercado = @idsubyacente
163
164 FETCH NEXT FROM cur_subyacentesPosViva
165 INTO @idsubyacente
166 END
167 CLOSE cur_subyacentesPosViva
168 DEALLOCATE cur_subyacentesPosViva
169
170 INSERT INTO Actividad (UUM, FechaHora, Fichero, Previsualizacion, Resultado, SistemaDestino, SistemaOrigen, TipoActividad, Visualizacion, IdUsuario)
171 VALUES ('DL_REPSOL\PROCESOS', GETDATE(), @file, 'Hermes_Captura30CSNA', 'Finalización de Operativa ' + @operativa, 'HERMES', @operativa, 'MarcadorPosViva', CONVERT(VARCHAR(23),
172 GETDATE(), 121), 59);
173 END TRY
174 BEGIN CATCH
175 INSERT INTO Actividad (UUM, FechaHora, Fichero, Previsualizacion, Resultado, SistemaDestino, SistemaOrigen, TipoActividad, Visualizacion, IdUsuario)
176 VALUES ('DL_REPSOL\PROCESOS', GETDATE(), @file, 'Hermes_Captura30CSNA', 'Error en INSERT de operativa ' + @operativa, 'HERMES', @operativa, 'MarcadorPosViva', CONVERT(VARCHAR(8000),
177 ERROR_MESSAGE()), 59);
178 END CATCH
179
180 BEGIN TRY
181 -- Se insertan en MarcadorPosViva_All todos los subyacentes que no se encuentren en ella para FechaRegistro = Hoy, activándose el bit "ExtraccionCSNA"
182 INSERT INTO MarcadorPosViva_All
183 SELECT sm.IdSubyacenteMercado, @fechaHoy, @fechaHoy, 0, 0, 0, 1 ExtraccionCSNA, 0, 0
184 FROM #IdSubyacenteMercadoTable sm
185 WHERE sm.IdSubyacenteMercado NOT IN (SELECT mp.IdSubyacenteMercado FROM MarcadorPosViva_All mp WHERE mp.FechaRegistro = @fechaHoy);
186
187 -- Se actualizan en MarcadorPosViva_All todos los subyacentes que se en cuentren en ella para FechaRegistro = Hoy,
188 -- activándose el bit "ExtraccionCSNA" si no se ha activado todavía para hoy
189 UPDATE MarcadorPosViva_All
190 SET ExtraccionCSNA = 1
191 WHERE IdSubyacenteMercado IN (SELECT mp.IdSubyacenteMercado FROM MarcadorPosViva_All mp WHERE mp.FechaRegistro = @fechaHoy and ExtraccionCSNA = 0)
192 AND IdSubyacenteMercado IN (SELECT sm.IdSubyacenteMercado FROM #IdSubyacenteMercadoTable sm)
193 AND FechaRegistro = @fechaHoy
194
195 INSERT INTO Actividad (UUM, FechaHora, Fichero, Previsualizacion, Resultado, SistemaDestino, SistemaOrigen, TipoActividad, Visualizacion, IdUsuario)
196 VALUES ('DL_REPSOL\PROCESOS', GETDATE(), @file, 'Hermes_Captura30CSNA', 'Finalización de Operativa ' + @operativa, 'HERMES', @operativa, 'MarcadorPosViva_All',
197 CONVERT(VARCHAR(23), GETDATE(), 121), 59);
198 END TRY
199 BEGIN CATCH
200 INSERT INTO Actividad (UUM, FechaHora, Fichero, Previsualizacion, Resultado, SistemaDestino, SistemaOrigen, TipoActividad, Visualizacion, IdUsuario)
201 VALUES ('DL_REPSOL\PROCESOS', GETDATE(), @file, 'Hermes_Captura30CSNA', 'Error en INSERT de operativa ' + @operativa, 'HERMES', @operativa, 'MarcadorPosViva_All', CONVERT(VARCHAR(8000),
202 ERROR_MESSAGE()), 59);
203 END CATCH
204
205 -- 3. Insertar en AlertaCSNA todos los registros que se encuentren en #DataTable donde la combinacion SubyRef-CodAllegro
206 -- para el dia de hoy no se encuentre insertada y el CodAllegro sea distinto de 'FIXED'.
207 -- Se actualiza en AlertaCSNA el campo [Existe_Hermes] a 1 para el dia de hoy con los SubyRef existentes en SubyacenteMercado
208 BEGIN TRY
209 INSERT INTO AlertaCSNA (FechaRegistro, SubyRef, CodAllegro, [VAR], [MtM], [Existe_Hermes], [Existe_ASE], [Trade_Vencido], [Mostrar_Alarma])
210 SELECT @fechaHoy FechaRegistro, SUBSTRING(SubyRef,CHARINDEX('CI#FwdC_', SubyRef)+LEN('CI#FwdC_'),LEN(SubyRef)), CodAllegro, 0 [VAR], 0 [MtM], 0 [Existe_Hermes], 0 [Existe_ASE], 0 [Trade_Vencido], 0 [Mostrar_Alarma]
211 FROM (
212 SELECT [campo30] SubyRef, [campo52] CodAllegro, COUNT(*) q FROM #DataTable
213 WHERE RTRIM(UPPER(ISNULL([campo52], '')))<>'FIXED' GROUP BY [campo30], [campo52]
214 ) q
215 WHERE ISNULL(q.subyref, 'SUBYREF_NULO') + ' ++ ' + ISNULL(q.CodAllegro, 'CODALLEGRO_NULO')
216 NOT IN ( SELECT ISNULL(r.subyref, 'SUBYREF_NULO') + ' ++ ' + ISNULL(r.CodAllegro, 'CODALLEGRO_NULO') FROM AlertaCSNA r WHERE r.FechaRegistro = @fechaHoy );
217
218 UPDATE AlertaCSNA SET [Existe_Hermes] = 1
219 WHERE FechaRegistro = @fechaHoy
220 AND SubyRef IN (SELECT DISTINCT(SubyRef) FROM SubyacenteMercado WHERE SubyRef IS NOT NULL);
221 END TRY
222 BEGIN CATCH
223 INSERT INTO Actividad (UUM, FechaHora, Fichero, Previsualizacion, Resultado, SistemaDestino, SistemaOrigen, TipoActividad, Visualizacion, IdUsuario)
224 VALUES ('DL_REPSOL\PROCESOS', GETDATE(), @file, 'Hermes_Captura30CSNA', 'Error de Operativa ' + @operativa + ' - INSERT en AlertaCSNA', 'HERMES',
225 @operativa, 'AlertaCSNA', CONVERT(VARCHAR(5), ERROR_LINE()) + ' -- ' + ERROR_MESSAGE(), 59);
226 END CATCH
227 END TRY
228 BEGIN CATCH
229 INSERT INTO Actividad (UUM, FechaHora, Fichero, Previsualizacion, Resultado, SistemaDestino, SistemaOrigen, TipoActividad, Visualizacion, IdUsuario)
230 VALUES ('DL_REPSOL\PROCESOS', GETDATE(), @file, 'Hermes_Captura30CSNA', 'Error de Operativa ' + @operativa, 'HERMES', @operativa, 'MarcadorPosViva_All', CONVERT(VARCHAR(5),
231 ERROR_LINE()) + ' -- ' + ERROR_MESSAGE(), 59);
232 END CATCH
233 END TRY
234 BEGIN CATCH
235 DECLARE @ERR_MSG VARCHAR(2000)
236 SET @ERR_MSG = ERROR_MESSAGE()
237 RAISERROR(@ERR_MSG, 16, 1)
238 END CATCH
239
240 --Elimino la tabla Temporal que se uso para leer el fichero
241 DROP TABLE #DataTable
242 DROP TABLE #DataTable2
243 DROP TABLE #IdSubyacenteMercadoTable
244END