Text ---- CREATE PROCEDURE dbo.spb_sp_valida_comprov_pericia ( @strXMLRemessa text, @xml_linha_generica varchar(8000), @ret_piloto int output ) AS BEGIN SET NOCOUNT ON DECLARE @numremessa varchar (20), @cd_msg varchar(10), @dt_hr_entrada datetime, @guid_operacao uniqueidentIFier, @NomPortRemessa varchar(100), @AgIF varchar(10), @CnpjRg varchar(20), @CodDpdnciaBC varchar(50), @xml_repet varchar(8000), @xml_repet_52 varchar(8000), @xml_repet_53 varchar(8000), @xml_repet_54 varchar(8000), @dtmovto varchar(10), @iposINicxml int, @iposfimxml int, @xmlnodeatual varchar(8000), @nomenode varchar(50), @Count int, @Ser varchar(20), @NumItem varchar(02), @VlrDen varchar(20), @point_repet binary(16), @str_repl_esq varchar(8000), @str_repl_dir varchar(8000), @posi_esq int, @posi_dir int, @len_replace int DECLARE @table_result TABLE ( numremessa varchar(20) COLLATE DATABASE_DEFAULT, NomPortRemessa varchar(100) COLLATE DATABASE_DEFAULT, AgIF varchar(10) COLLATE DATABASE_DEFAULT, CnpjRg varchar(20) COLLATE DATABASE_DEFAULT, CodDpdnciaBC varchar(50) COLLATE DATABASE_DEFAULT, NumItem varchar(4) COLLATE DATABASE_DEFAULT, VlrDen varchar(20) COLLATE DATABASE_DEFAULT, Ser varchar(20) COLLATE DATABASE_DEFAULT, CodMunic varchar(10) COLLATE DATABASE_DEFAULT, Ds_Munic varchar(60) COLLATE DATABASE_DEFAULT, DtSuspc varchar(10) COLLATE DATABASE_DEFAULT, DtMovto varchar(10) COLLATE DATABASE_DEFAULT, ResultPericia varchar(50) COLLATE DATABASE_DEFAULT, str_xml text COLLATE DATABASE_DEFAULT ) DECLARE @table_result_tmp TABLE ( NumItem varchar(4) COLLATE DATABASE_DEFAULT, VlrDen varchar(20) COLLATE DATABASE_DEFAULT, Ser varchar(20) COLLATE DATABASE_DEFAULT, CodMunic varchar(10) COLLATE DATABASE_DEFAULT, Ds_Munic varchar(60) COLLATE DATABASE_DEFAULT, DtSuspc varchar(10) COLLATE DATABASE_DEFAULT, DtMovto varchar(10) COLLATE DATABASE_DEFAULT, ResultPericia varchar(20) COLLATE DATABASE_DEFAULT ) DECLARE @table_oper TABLE ( guid_operacao uniqueidentIFier , cd_msg char(10) , dt_hr_entrada datetime ) SET @numremessa = dbo.spb_fc_get_node_value(@strXMLRemessa, 'NumRemessa') IF @numremessa IS NULL OR @numremessa = '' BEGIN SET @numremessa = dbo.spb_fc_get_node_value(@strXMLRemessa, 'NumRemessaOr') END UPDATE spb_tb_pil_operacoes SET num_remessa = @numremessa WHERE nr_ctrl_if = dbo.spb_fc_get_node_value(@strXMLRemessa, 'NumCtrlIF') UPDATE @table_result SET str_xml = tab_upd.str_xml FROM ( SELECT str_xml FROM spb_tb_pil_operacoes (NOLOCK) WHERE num_remessa = @numremessa AND cd_msg IN ('CIR0051', 'CIR0061') ) AS tab_upd INSERT INTO @table_oper SELECT a.guid_operacao, a.cd_msg, a.dt_hr_entrada FROM spb_tb_msg_ctrl_operacoes a WITH (NOLOCK) JOIN spb_tb_pil_operacoes b WITH (NOLOCK) ON b.guid_operacao = a.guid_operacao WHERE b.num_remessa = @numremessa AND a.cd_msg = b.cd_msg AND b.st_operacao = 4 DECLARE cursor_oper CURSOR FOR SELECT guid_operacao, RTRIM(LTRIM(cd_msg)), dt_hr_entrada FROM @table_oper ORDER BY dt_hr_entrada OPEN cursor_oper FETCH NEXT FROM cursor_oper INTO @guid_operacao, @cd_msg, @dt_hr_entrada WHILE @@FETCH_STATUS = 0 BEGIN DELETE FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao IF @cd_msg = 'CIR0051' BEGIN SELECT @NomPortRemessa = dbo.spb_fc_get_node_value(str_xml, 'NomPortRemessa'), @AgIF = dbo.spb_fc_get_node_value(str_xml, 'AgIF'), @CnpjRg = ISNULL(dbo.spb_fc_get_node_value(str_xml, 'NumIdentddPortRemessa'), dbo.spb_fc_get_node_value(str_xml, 'CNPJPortRemessa')), @CodDpdnciaBC = dbo.spb_fc_get_node_value(str_xml, 'CodDpdnciaBC') FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg INSERT INTO spb_tb_msg_repet_pericia SELECT @guid_operacao, str_xml FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg SELECT @point_repet = TEXTPTR(txt_repet), @str_repl_esq = SUBSTRING(txt_repet, 1, PATINDEX('%%', txt_repet) -1) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_esq) SET @posi_esq = 0 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_esq @len_replace '' -- INI - Book 3.03 /* SELECT @posi_dir = PATINDEX('%%', txt_repet), @str_repl_dir = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), DATALENGTH(txt_repet)) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_dir) - 1 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_dir @len_replace '' */ -- FIM - Book 3.03 SET @xmlnodeatual = '.' WHILE @xmlnodeatual <> '' BEGIN SELECT @point_repet = TEXTPTR(txt_repet), @xmlnodeatual = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), PATINDEX('%%', txt_repet) - PATINDEX('%%', txt_repet) + LEN('')) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao -- INI - Book 3.03 IF LEFT(@xmlnodeatual, 7) <> ' LEN('') BEGIN INSERT INTO @table_result ( numitem, vlrden, ser, codmunic, dtsuspc ) VALUES ( CAST(dbo.spb_fc_get_node_value(@xmlnodeatual, 'NumItem') AS int), dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrDen'), dbo.spb_fc_get_node_value(@xmlnodeatual, 'Ser'), dbo.spb_fc_get_node_value(@xmlnodeatual, 'CodMunic'), RIGHT( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') ,2) + '/' + SUBSTRING( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') , 6, 2 ) + '/' + LEFT( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') ,4) ) END SET @len_replace = LEN(@xmlnodeatual) UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet 0 @len_replace '' END END IF @cd_msg = 'CIR0061' BEGIN SELECT @NomPortRemessa = dbo.spb_fc_get_node_value(str_xml, 'NomPortRemessa'), @AgIF = dbo.spb_fc_get_node_value(str_xml, 'AgIF'), @CnpjRg = ISNULL(dbo.spb_fc_get_node_value(str_xml, 'NumIdentddPortRemessa'), dbo.spb_fc_get_node_value(str_xml, 'CNPJPortRemessa')), @CodDpdnciaBC = dbo.spb_fc_get_node_value(str_xml, 'CodDpdnciaBC') FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg INSERT INTO spb_tb_msg_repet_pericia SELECT @guid_operacao, str_xml FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg INSERT INTO @table_result ( numitem, vlrden, ser, codmunic, dtsuspc ) VALUES ( 1, dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrEstmd'), 'N/I', 'N/I', CONVERT(varchar(10), getdate(), 103) ) END IF @cd_msg = 'CIR0052' BEGIN SELECT @NomPortRemessa = CASE WHEN ltrim (dbo.spb_fc_get_node_value(str_xml, 'NomPortRemessa') ) = '' THEN @NomPortRemessa ELSE dbo.spb_fc_get_node_value(str_xml, 'NomPortRemessa') END , @CnpjRg = CASE WHEN ltrim ( ISNULL(dbo.spb_fc_get_node_value (str_xml, 'NumIdentddPortRemessa'), dbo.spb_fc_get_node_value(str_xml, 'CNPJPortRemessa'))) = '' THEN @CnpjRg ELSE ISNULL( dbo.spb_fc_get_node_value(str_xml,'NumIdentddPortRemessa') , dbo.spb_fc_get_node_value(str_xml,'CNPJPortRemessa') ) END , @CodDpdnciaBC = CASE WHEN ltrim ( dbo.spb_fc_get_node_value(str_xml, 'CodDpdnciaBC') ) = '' THEN @CodDpdnciaBC ELSE dbo.spb_fc_get_node_value(str_xml, 'CodDpdnciaBC') END FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg INSERT INTO spb_tb_msg_repet_pericia SELECT @guid_operacao, str_xml FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg SELECT @point_repet = TEXTPTR(txt_repet), @str_repl_esq = SUBSTRING(txt_repet, 1, PATINDEX('%%', txt_repet) - 1) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_esq) SET @posi_esq = 0 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_esq @len_replace '' -- INI - Book 3.03 /* SELECT @posi_dir = PATINDEX('%%', txt_repet), @str_repl_dir = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), DATALENGTH(txt_repet)) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_dir) - 1 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_dir @len_replace '' */ -- FIM - Book 3.03 SET @xmlnodeatual = '.' WHILE @xmlnodeatual <> '' BEGIN SELECT @point_repet = TEXTPTR(txt_repet), @xmlnodeatual = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), PATINDEX('%%', txt_repet) - PATINDEX('%%', txt_repet) + LEN('')) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao -- INI - Book 3.03 IF LEFT(@xmlnodeatual, 7) <> ' LEN('') BEGIN UPDATE @table_result SET vlrden = CASE WHEN ltrim(dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrDen')) = '' THEN vlrden ELSE dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrDen') END, ser = CASE WHEN dbo.spb_fc_get_node_value (@xmlnodeatual, 'Ser') = '' THEN ser ELSE dbo.spb_fc_get_node_value (@xmlnodeatual, 'Ser') END, codmunic = CASE WHEN dbo.spb_fc_get_node_value (@xmlnodeatual, 'CodMunic') = '' THEN codmunic ELSE dbo.spb_fc_get_node_value (@xmlnodeatual, 'CodMunic') END, dtsuspc = CASE WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') = '' THEN dtsuspc ELSE RIGHT(dbo.spb_fc_get_node_value(@xmlnodeatual,'DtSuspc'),2) + '/' + SUBSTRING( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') , 6, 2 ) + '/' + LEFT( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') ,4) END WHERE CAST(dbo.spb_fc_get_node_value(@xmlnodeatual, 'NumItem')AS int) = CAST(numitem AS int) END SET @len_replace = LEN(@xmlnodeatual) UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet 0 @len_replace '' END END IF @cd_msg = 'CIR0053' BEGIN INSERT INTO spb_tb_msg_repet_pericia SELECT @guid_operacao, str_xml FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg SELECT @point_repet = TEXTPTR(txt_repet), @str_repl_esq = SUBSTRING(txt_repet, 1, PATINDEX('%%', txt_repet) - 1) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_esq) SET @posi_esq = 0 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_esq @len_replace '' -- INI - Book 3.03 /* SELECT @posi_dir = PATINDEX('%%', txt_repet), @str_repl_dir = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), DATALENGTH(txt_repet)) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_dir) - 1 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_dir @len_replace '' */ -- FIM - Book 3.03 SET @xmlnodeatual = '.' WHILE @xmlnodeatual <> '' BEGIN SELECT @point_repet = TEXTPTR(txt_repet), @xmlnodeatual = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), PATINDEX('%%', txt_repet) - PATINDEX('%%', txt_repet) + LEN('')) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao -- INI - Book 3.03 IF LEFT(@xmlnodeatual, 7) <> ' LEN('') BEGIN INSERT INTO @table_result ( numitem, vlrden, ser, codmunic, dtsuspc ) VALUES ( CAST(dbo.spb_fc_get_node_value(@xmlnodeatual, 'NumItem')AS int) , dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrDen') , dbo.spb_fc_get_node_value(@xmlnodeatual, 'Ser') , dbo.spb_fc_get_node_value(@xmlnodeatual, 'CodMunic') , RIGHT( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') ,2) + '/' + SUBSTRING( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') , 6, 2 ) + '/' + LEFT( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') ,4) ) END SET @len_replace = LEN(@xmlnodeatual) UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet 0 @len_replace '' END END IF @cd_msg = 'CIR0054' BEGIN INSERT INTO spb_tb_msg_repet_pericia SELECT @guid_operacao, str_xml FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao AND cd_msg = @cd_msg + 'R1' IF @@rowcount <> 0 BEGIN DELETE FROM @table_result_tmp INSERT INTO @table_result_tmp SELECT NumItem, VlrDen, Ser, CodMunic, Ds_Munic, DtSuspc, DtMovto, ResultPericia FROM @table_result DELETE FROM @table_result END SELECT @point_repet = TEXTPTR(txt_repet), @str_repl_esq = SUBSTRING(txt_repet, 1, PATINDEX('%%', txt_repet) - 1) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_esq) SET @posi_esq = 0 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_esq @len_replace '' -- INI - Book 3.03 /* SELECT @posi_dir = PATINDEX('%%', txt_repet), @str_repl_dir = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), DATALENGTH(txt_repet)) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_dir) - 1 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_dir @len_replace '' */ -- FIM - Book 3.03 SET @xmlnodeatual = '.' WHILE @xmlnodeatual <> '' BEGIN SELECT @point_repet = TEXTPTR(txt_repet), @xmlnodeatual = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), PATINDEX('%%', txt_repet) - PATINDEX('%%', txt_repet) + LEN('')) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao -- INI - Book 3.03 IF LEFT(@xmlnodeatual, 7) <> ' LEN('') BEGIN INSERT INTO @table_result ( numitem, vlrden, ser, codmunic, dtsuspc ) VALUES ( CAST(dbo.spb_fc_get_node_value(@xmlnodeatual, 'NumItem')AS int) , dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrDen') , dbo.spb_fc_get_node_value(@xmlnodeatual, 'Ser') , dbo.spb_fc_get_node_value(@xmlnodeatual, 'CodMunic') , RIGHT( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') ,2) + '/' + SUBSTRING( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') , 6, 2 ) + '/' + LEFT( dbo.spb_fc_get_node_value(@xmlnodeatual, 'DtSuspc') ,4) ) END SET @len_replace = LEN(@xmlnodeatual) UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet 0 @len_replace '' END DECLARE cursor_cir0054 CURSOR FOR SELECT CAST(NumItem AS int), VlrDen, Ser FROM @table_result OPEN cursor_cir0054 FETCH NEXT FROM cursor_cir0054 INTO @NumItem, @VlrDen, @Ser WHILE @@FETCH_STATUS = 0 BEGIN UPDATE @table_result SET DtMovto = b.DtMovto, ResultPericia = b.ResultPericia FROM @table_result a, @table_result_tmp b WHERE a.Ser = b.Ser AND a.CodMunic = b.CodMunic AND a.VlrDen = b.VlrDen FETCH NEXT FROM cursor_cir0054 INTO @NumItem, @VlrDen, @Ser END CLOSE cursor_cir0054 DEALLOCATE cursor_cir0054 END IF @cd_msg = 'CIR0058' BEGIN SELECT @DtMovto = dbo.spb_fc_get_node_value(str_xml, 'DtMovto') FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao INSERT INTO spb_tb_msg_repet_pericia SELECT @guid_operacao, str_xml FROM spb_tb_msg_ctrl_operacoes (NOLOCK) WHERE guid_operacao = @guid_operacao SELECT @point_repet = TEXTPTR(txt_repet), @str_repl_esq = SUBSTRING(txt_repet, 1, PATINDEX('%%', txt_repet) - 1) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_esq) SET @posi_esq = 0 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_esq @len_replace '' -- INI - Book 3.03 /* SELECT @posi_dir = PATINDEX('%%', txt_repet), @str_repl_dir = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), DATALENGTH(txt_repet)) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao SET @len_replace = LEN(@str_repl_dir) - 1 UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet @posi_dir @len_replace '' */ -- FIM - Book 3.03 SET @xmlnodeatual = '.' WHILE @xmlnodeatual <> '' BEGIN SELECT @point_repet = TEXTPTR(txt_repet), @xmlnodeatual = SUBSTRING(txt_repet, PATINDEX('%%', txt_repet), PATINDEX('%%', txt_repet) - PATINDEX('%%', txt_repet) + LEN('')) FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao -- INI - Book 3.03 IF LEFT(@xmlnodeatual, 7) <> ' LEN('') BEGIN UPDATE @table_result SET ResultPericia = CASE WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '1' THEN '1 - FALSO' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '2' THEN '2 - LEGÍTIMO' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '3' THEN '3 - SEM VALOR' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '4' THEN '4 - Falso - antifurto' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '5' THEN '5 - Legítimo - antifurto' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '6' THEN '6 - Sem valor - antifurto' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '7' THEN '7 - Retido - antifurto' END, DtMovto = RIGHT(@DtMovto, 2) + '/' + SUBSTRING(@DtMovto, 6, 2) + '/' + LEFT(@DtMovto, 4), VlrDen = dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrDen') WHERE CAST(NumItem AS int) = CAST(dbo.spb_fc_get_node_value(@xmlnodeatual, 'NumItem') AS int) IF @@ROWCOUNT = 0 BEGIN INSERT INTO @table_result SELECT numremessa, NomPortRemessa, AgIF, CnpjRg, CodDpdnciaBC, dbo.spb_fc_get_node_value(@xmlnodeatual, 'NumItem'), dbo.spb_fc_get_node_value(@xmlnodeatual, 'VlrDen'), Ser, CodMunic, Ds_Munic, DtSuspc, RIGHT(@DtMovto, 2) + '/' + SUBSTRING(@DtMovto, 6, 2) + '/' + LEFT(@DtMovto, 4), CASE WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '1' THEN '1 - FALSO' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '2' THEN '2 - LEGÍTIMO' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '3' THEN '3 - SEM VALOR' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '4' THEN '4 - Falso - antifurto' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '5' THEN '5 - Legítimo - antifurto' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '6' THEN '6 - Sem valor - antifurto' WHEN dbo.spb_fc_get_node_value(@xmlnodeatual, 'ResultPericia') = '7' THEN '7 - Retido - antifurto' END, @strXMLRemessa FROM @table_result WHERE NumItem = 1 END END SET @len_replace = LEN(@xmlnodeatual) UPDATETEXT spb_tb_msg_repet_pericia.txt_repet @point_repet 0 @len_replace '' END END FETCH NEXT FROM cursor_oper INTO @guid_operacao , @cd_msg, @dt_hr_entrada END DELETE FROM spb_tb_msg_repet_pericia WHERE guid_operacao = @guid_operacao CLOSE cursor_oper DEALLOCATE cursor_oper UPDATE @table_result SET numremessa = @numremessa, NomPortRemessa = @NomPortRemessa, AgIF = @AgIF, CnpjRg = @CnpjRg, CodDpdnciaBC = @CodDpdnciaBC, ds_munic = b.ds_munic FROM @table_result a LEFT OUTER JOIN spb_tb_gen_municipios b WITH (NOLOCK) ON b.cd_munic = CASE WHEN ISNUMERIC(a.codmunic) = 1 THEN a.codmunic ELSE -1 END IF EXISTS(SELECT numremessa FROM spb_tb_pil_ctrl_remessa_bmb WHERE numremessa = @numremessa ) BEGIN DELETE FROM spb_tb_pil_ctrl_remessa_bmb WHERE numremessa = @numremessa END UPDATE @table_result SET str_xml = tab_upd.str_xml FROM ( SELECT str_xml FROM spb_tb_pil_operacoes (NOLOCK) WHERE num_remessa = @numremessa AND cd_msg IN ('CIR0051', 'CIR0061') ) AS tab_upd INSERT INTO spb_tb_pil_ctrl_remessa_bmb SELECT numremessa, NomPortRemessa, AgIF, CnpjRg, CodDpdnciaBC, CAST(NumItem AS int), VlrDen, Ser, CodMunic, Ds_Munic, DtSuspc, DtMovto, ResultPericia, str_xml FROM @table_result SET NOCOUNT OFF END