Text
----


CREATE PROCEDURE dbo.spb_sp_desdobra_msg_aut (

				              @str_xml            varchar(max),

				              @xml_linha_generica varchar(max),

                                              @retorno            int OUTPUT

                                             )

AS

BEGIN



	SET NOCOUNT ON

	

	DECLARE @guid_operacao		varchar(max),

		@str_xml_de		varchar(max),

		@str_xml_R1		varchar(max),

		@str_xml_para		varchar(max),

		@str_xml_marcado	varchar(max),

		@vlr_forcado		varchar(100),

		@cod_msg_de		varchar(10),

		@cod_msg_para		varchar(10),

		@dom_spb		varchar(5),

		@num_ctrl_if_para	varchar(20),

		@ispb_clear		varchar(8),

		@ispbif			varchar(8),

		@strong_id		varchar(max),

		@dt_char		varchar(8),

		@NUOp			varchar(23),

		@str_aux		nvarchar(4000),

		@str_aux2		nvarchar(4000),

		@inst_financ		varchar(10),

		@ispb_ent_destino	varchar(8),

		@str_sql		nvarchar(4000),

		@cnt			int,

		@vrfOK			int,

		@id_tag_numctrl		varchar(100),

		@id_tag_ispbif		varchar(100),

		@id_tag_vlr		varchar(100),

		@xml_aux		nvarchar(max)

	

	SET @cod_msg_de    = dbo.spb_fc_get_node_value(@str_xml, 'CodMsg')

	SET @inst_financ   = dbo.spb_fc_get_node_value(@xml_linha_generica, 'GENInstFinanc')

	SET @guid_operacao = dbo.spb_fc_get_node_value(@xml_linha_generica, 'GENGUIDOperacao')



	IF EXISTS(SELECT 1

	            FROM spb_tb_pil_operacoes   a (NOLOCK),

	                 spb_tb_gen_status_oper b (NOLOCK)

	           WHERE b.st_operacao          = a.st_operacao

	             AND a.guid_operacao        = @guid_operacao

	             AND b.flg_permite_desdobro = 'N'

	         ) OR 

	   EXISTS(SELECT 1

	            FROM spb_tb_pil_operacoes a (NOLOCK)

	           WHERE correl_id      = dbo.spb_fc_get_node_value(@str_xml, 'NUOP')

		     AND flg_desdobrada = 1

	         ) OR 

	   EXISTS(SELECT 1

	            FROM spb_tb_gen_msg_desdobro_aut (NOLOCK)

		   WHERE id_inst_financ = @inst_financ

		     AND cd_msg_de      = @cod_msg_de

		     AND flg_ativo      = 0) BEGIN

	   SET @retorno = 0

	   RETURN

	END



	SELECT TOP 1 

	       @cod_msg_para = cd_msg_para

	  FROM spb_tb_gen_msg_desdobro (NOLOCK)

	 WHERE cd_msg_de        = @cod_msg_de

	   AND flg_desdobro_aut = 1

	

	IF @cod_msg_para IS NULL OR @cod_msg_para = '' BEGIN

	   SET @retorno = 1

	   RETURN

	END



	BEGIN TRANSACTION



	SELECT @strong_id = RTRIM(ds_strong_id)

	  FROM spb_tb_pil_strong_id (NOLOCK)

	 WHERE guid_operacao = @guid_operacao



	EXEC dbo.spb_sp_gera_numctrlif @inst_financ, @cod_msg_para, @num_ctrl_if_para OUTPUT, 0



	IF RIGHT(@cod_msg_de, 2) = 'R1' BEGIN

	   SET @str_xml_R1 = @str_xml



	   SELECT @str_xml_de = str_xml

	     FROM spb_tb_pil_operacoes (NOLOCK)

	    WHERE guid_operacao = @guid_operacao

	END

	ELSE BEGIN

	   SELECT @str_xml_de = str_xml

	     FROM spb_tb_pil_operacoes (NOLOCK)

	    WHERE guid_operacao = @guid_operacao



	   SELECT @str_xml_R1 = str_xml

	     FROM spb_tb_msg_ctrl_operacoes (NOLOCK)

	    WHERE guid_operacao = @guid_operacao

	      AND cd_msg = @cod_msg_de + 'R1'

	END

	

	SET @str_xml_para    = dbo.spb_fc_monta_xml_bc_limpo(@cod_msg_para)

	SET @str_xml_marcado = dbo.spb_fc_monta_xml_bc_varchar(@cod_msg_para)

	

	-- Inicio - Troca de Tags SISMSG

	DECLARE cur_desdobro CURSOR STATIC LOCAL FOR

		SELECT cd_msg_de, cd_msg_para, tag_repet, tag_de, tag_para, tag_dependencia, valor_tag, ds_formula_de_para, ispb_ent_externa, ISNULL(vlr_forcado, '')

		  FROM spb_tb_gen_msg_desdobro (NOLOCK)

		 WHERE cd_msg_de        = @cod_msg_de

 	           AND flg_desdobro_aut = 1

	           AND ISNULL(tag_repet, '') = ''

	

	OPEN cur_desdobro

	

	DECLARE @cd_msg_de		varchar(10),

		@cd_msg_para		varchar(10),

		@tag_repet		varchar(100),

		@tag_de			varchar(100),

		@tag_para		varchar(100),

		@tag_dependencia	varchar(100),

		@valor_tag		varchar(100),

		@ds_formula_de_para	varchar(500),

		@ispb_ent_externa	varchar(8)

	

	FETCH NEXT FROM cur_desdobro INTO @cd_msg_de, @cd_msg_para, @tag_repet, @tag_de, @tag_para, @tag_dependencia, @valor_tag, @ds_formula_de_para, @ispb_ent_externa, @vlr_forcado

	WHILE @@FETCH_STATUS = 0 BEGIN

	   IF NOT @ispb_ent_externa IS NULL BEGIN

	      SET @ispb_ent_destino = @ispb_ent_externa

	   END



	   IF RIGHT(@cd_msg_de, 2) <> 'R1' BEGIN

	      SET @str_aux = dbo.spb_fc_get_node_value(@str_xml_de, RTRIM(@tag_de))

	   END

	   ELSE BEGIN

	      SET @str_aux = dbo.spb_fc_get_node_value(@str_xml_R1, RTRIM(@tag_de))

	   END



	   IF @ds_formula_de_para <> '' BEGIN

	      -- Em alguns casos o tratamento para o desdobro Automático, como no caso de datas, os valores diferem.

	      -- Manual a data deve ser: 01/01/1900

	      -- Automático a data deve ser: 19000101

	      -- Por isso o default Manual é setado para automático e dentro da function este valor deve ser lido para

	      -- assim retornar o valor correto conforme a origem.

	      -- Ex.: a FC 'spb_fc_trata_CIR0015_CIR0012'

	      SET @ds_formula_de_para = REPLACE(@ds_formula_de_para, 'MANUAL', 'AUTOMATICO')



	      IF CHARINDEX(':=', @ds_formula_de_para) > 0 BEGIN

	         --Fórmula como as da gen_msg_detalhe ...

	         SET @str_sql = 'SELECT @str_aux = dbo.spb_fc_interpreta_valor_tag('''+@ds_formula_de_para+''', '''+@str_aux+''','''+@str_aux+''')'

	      END

	      IF CHARINDEX('dbo.', @ds_formula_de_para) > 0 BEGIN

	         -- Function ...

	         SET @ds_formula_de_para = Replace(@ds_formula_de_para, '#valor', @str_aux) -- Pode ter um parâmetro #valor...

	         SET @str_sql = 'SELECT @str_aux = ' + @ds_formula_de_para

	      END

	      ELSE BEGIN  --Constante ...

	         SET @ds_formula_de_para = Replace(@ds_formula_de_para, '#valor', @str_aux) -- Pode ter um parâmetro #valor...

	         SET @str_sql = 'SELECT @str_aux = ' + @ds_formula_de_para

	      END



	      EXEC sp_executesql @str_sql,

	                         N'@str_aux nvarchar(4000) OUTPUT',

	                         @str_aux OUTPUT

	   END



	   SET @vrfOK = 0



	   -- Inicio - Leandro - em 26/02/2004 - Ficha: 35980

	   IF NOT @tag_dependencia IS NULL OR @tag_dependencia <> '' BEGIN

	      SET @vrfOK = 1



	      IF CHARINDEX('>', @valor_tag) = 0 AND CHARINDEX('<', @valor_tag) = 0 BEGIN

	         IF CHARINDEX(LTRIM(RTRIM(dbo.spb_fc_get_node_value(@str_xml_de, @tag_dependencia))), @valor_tag + ',') = 0 BEGIN

	            SET @vrfOK = 0

	         END

	      END

	      ELSE IF CHARINDEX('@xml_aux', @valor_tag) <> 0 BEGIN

	         SET @str_aux2 = REPLACE(dbo.spb_fc_get_node_value(@str_xml_de, @tag_dependencia), ',', '.')



		 IF @str_aux2 <> '' BEGIN

	            SET @str_sql  = 'SELECT @retorno = CASE WHEN ''' + @str_aux2 + ''' '+@valor_tag+' THEN 1 ELSE 0 END'

	            EXEC sp_executeSQL @str_sql, 

	                               N'@xml_aux nvarchar(max), @retorno int OUTPUT', 

				       @xml_aux,

		   		       @retorno OUTPUT

		 END

	      END

	      ELSE BEGIN

	         IF CHARINDEX(@tag_dependencia, @str_xml_de) <> 0 BEGIN

	            SET @str_aux2 = REPLACE(dbo.spb_fc_get_node_value(@str_xml_de, @tag_dependencia), ',', '.')



		    IF @str_aux2 <> '' BEGIN

		       IF CHARINDEX('<', @valor_tag) <> 0 OR CHARINDEX('>', @valor_tag) <> 0 BEGIN

		          SET @str_sql  = 'SELECT @vrfOK = CASE WHEN CAST(''' + @str_aux2 + ''' AS numeric(20, 2)) '+@valor_tag+' THEN 1 ELSE 0 END'

		       END

		       ELSE BEGIN

	                  SET @str_sql  = 'SELECT @vrfOK = CASE WHEN ''' + @str_aux2 + ''' '+@valor_tag+' THEN 1 ELSE 0 END'

		       END



	               EXEC sp_executeSQL @str_sql, N'@vrfOK int OUTPUT', @vrfOK OUTPUT

		    END

	         END

	      END



	      IF @vrfOK = 0 BEGIN

	         SET @retorno = 1

	         CLOSE cur_desdobro

	         DEALLOCATE cur_desdobro

	         COMMIT TRANSACTION

	         RETURN

	      END

	   END

	   -- Término - Leandro - em 26/02/2004 - Ficha: 35980



	   IF @vlr_forcado <> '' BEGIN

	      SET @str_aux = @vlr_forcado

	   END



	   IF LTRIM(RTRIM(@str_aux)) <> '' BEGIN

	      SET @str_xml_para = dbo.spb_fc_set_node_value(@str_xml_para, 'SISMSG', RTRIM(@tag_para), @str_aux)

	   END

	   

	   IF LEFT(@tag_para, 4) = 'DtHr' BEGIN

	      SET @str_aux = SUBSTRING(@str_aux, 9, 2) + SUBSTRING(@str_aux, 6, 2) + SUBSTRING(@str_aux, 1, 4) +

                             REPLACE(SUBSTRING(@str_aux, 12, 8), ':', '')

	   END

	   ELSE IF LEFT(@tag_para, 2) = 'Dt' BEGIN

	      SET @str_aux = SUBSTRING(@str_aux, 9, 2) + SUBSTRING(@str_aux, 6, 2) + SUBSTRING(@str_aux, 1, 4)

	   END



	   IF LTRIM(RTRIM(@str_aux)) <> '' BEGIN

	      SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'SISMSG', RTRIM(@tag_para), @str_aux)

	   END

	   

	   FETCH NEXT FROM cur_desdobro INTO @cd_msg_de, @cd_msg_para, @tag_repet, @tag_de, @tag_para, @tag_dependencia, @valor_tag, @ds_formula_de_para, @ispb_ent_externa, @vlr_forcado

	END



	CLOSE cur_desdobro

	DEALLOCATE cur_desdobro

	-- Fim - Troca de Tags SISMSG



	SET @dt_char = REPLACE(CONVERT(varchar(10), dbo.spb_fc_get_node_value(@str_xml_de, 'DtMovto'), 112), '-', '')

	SET @ispbif  = dbo.spb_fc_get_node_value(@str_xml_de, 'IdentdDestinatario')



	SELECT @dom_spb = dom_spb

	  FROM spb_tb_gen_msg_detalhe (NOLOCK)

	 WHERE cd_msg = @cod_msg_para



	SET @dom_spb = ISNULL(@dom_spb, 'SPB01')

	

	EXEC dbo.spb_sp_gera_nuop @ispbif, @inst_financ, @NUOp OUTPUT, @dt_char, @dom_spb



	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'IdentdEmissor', @ispbif)

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'BCMSG', 'IdentdEmissor', @ispbif)



	IF EXISTS(SELECT 1 

	            FROM spb_tb_gen_msg_desdobro_msg_ent_ext (NOLOCK) 

	           WHERE cd_msg = @cod_msg_para 

	             AND ispb_origem = dbo.spb_fc_get_node_value(@str_xml_de, 'IdentdEmissor')

	         ) BEGIN

	   SELECT @ispb_ent_destino = ispb_destino

	     FROM spb_tb_gen_msg_desdobro_msg_ent_ext (NOLOCK) 

	    WHERE cd_msg      = @cod_msg_para 

	      AND ispb_origem = dbo.spb_fc_get_node_value(@str_xml_de, 'IdentdEmissor')

	END



	IF @ispb_ent_destino IS NULL OR @ispb_ent_destino = '' BEGIN

	   SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'IdentdDestinatario', dbo.spb_fc_get_node_value(@str_xml_de, 'IdentdEmissor'))

	   SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'BCMSG', 'IdentdDestinatario', dbo.spb_fc_get_node_value(@str_xml_de, 'IdentdEmissor'))

	END

	ELSE BEGIN

	   SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'IdentdDestinatario', @ispb_ent_destino)

	   SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'BCMSG', 'IdentdDestinatario', @ispb_ent_destino)

	END



	SELECT @id_tag_numctrl = id_tag_numctrl,

	       @id_tag_ispbif  = id_tag_ispbif,

	       @id_tag_vlr     = REPLACE(REPLACE(id_tag_vlr, '[', ''), ']', '')

	  FROM spb_tb_gen_msg_detalhe (NOLOCK)

	 WHERE cd_msg = @cod_msg_para



	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'SISMSG', 'CodMsg',        @cod_msg_para)

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'SISMSG', 'CodMsg',        @cod_msg_para)

	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'SISMSG', @id_tag_numctrl, @num_ctrl_if_para)

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'SISMSG', @id_tag_numctrl, @num_ctrl_if_para)

	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'SISMSG', @id_tag_ispbif,  @ispbif)

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'SISMSG', @id_tag_ispbif,  @ispbif)



	IF dbo.spb_fc_get_node_value(@str_xml_para, 'DtMovto') = '' BEGIN

	   SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para, 'SISMSG', 'DtMovto', dbo.spb_fc_get_node_value(@str_xml_de, 'DtMovto'))



	   SET @str_aux = dbo.spb_fc_get_node_value(@str_xml_de, 'DtMovto')

	   SET @str_aux = SUBSTRING(@str_aux, 9, 2) + '/' + SUBSTRING(@str_aux, 6, 2) + '/' + SUBSTRING(@str_aux, 1, 4)

	   SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'SISMSG', 'DtMovto', @str_aux)

	END

	

	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'NumSeq',   '001')

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'BCMSG', 'NumSeq',   '001')

	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'IndrCont', 'N')

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'BCMSG', 'IndrCont', 'N')



	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'DomSist', @dom_spb)

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'DomSist', @dom_spb)



	SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'BCMSG', 'NUOp', @NUOp)

	SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'BCMSG', 'NUOp', @NUOp)



	IF CHARINDEX('DtHrIF', @str_xml_para) > 0 BEGIN

	   SET @str_xml_para    = dbo.spb_fc_set_node_value(@str_xml_para,    'SISMSG', 'DtHrIF', REPLACE(CONVERT(varchar(10), getdate(), 103), '/', '') + REPLACE(CONVERT(varchar(8), getdate(), 114), ':', ''))

	   SET @str_xml_marcado = dbo.spb_fc_set_node_value(@str_xml_marcado, 'SISMSG', 'DtHrIF', REPLACE(CONVERT(varchar(10), getdate(), 103), '/', '') + REPLACE(CONVERT(varchar(8), getdate(), 114), ':', ''))

	END



	-- Inicio - Troca de Tags Repetição

	DECLARE cur_desdobro CURSOR STATIC LOCAL FOR

		SELECT DISTINCT 

		       REPLACE(REPLACE(REPLACE(id_tag , 'Repet_', ''), 'Grupo_', ''), RTRIM(cd_msg) + '_', '')

		  FROM spb_tb_gen_msg_tags_bc (NOLOCK)

		 WHERE cd_msg           = @cd_msg_para

		   AND LEFT(id_tag, 6) IN ('Repet_', 'Grupo_')

		   AND min_occurs      <> '0'



	OPEN cur_desdobro



	FETCH NEXT FROM cur_desdobro INTO @tag_repet

	WHILE @@FETCH_STATUS = 0 BEGIN

	   SET @str_xml_para    = dbo.spb_fc_troca_repet_desdobro(@str_xml, @str_xml_para,    @tag_repet)

	   SET @str_xml_marcado = dbo.spb_fc_troca_repet_desdobro(@str_xml, @str_xml_marcado, @tag_repet)



	   FETCH NEXT FROM cur_desdobro INTO @tag_repet

	END



	CLOSE cur_desdobro

	DEALLOCATE cur_desdobro

	-- Fim - Troca de Tags Repetição



	SET @strong_id = dbo.spb_fc_set_node_value(@strong_id, 'STRONGID', 'GENAbrevLegado', 'DESDAUT')

	SET @strong_id = dbo.spb_fc_set_node_value(@strong_id, 'STRONGID', 'IdLegado', 'DESDAUT')



	-- LLP - 20/08/2019

	-- Previne geração de valores duplicados, devido a algumas mensagens que podem ser recebidas em duplicidade

	-- Com isso somente uma será desdobrada

	IF (@cod_msg_de <> 'LTR0001') OR

	   (@cod_msg_de = 'LTR0001' and @cod_msg_para <> 'LTR0004') OR

	   NOT EXISTS(SELECT 1

	                FROM spb_tb_pil_operacoes (NOLOCK)

	               WHERE dt_movto         = dbo.spb_fc_get_node_value(@str_xml, 'DtMovto')

	                 AND cd_msg           = @cod_msg_de

	                 AND ABS(vl_operacao) = ABS(dbo.spb_fc_get_node_value(@str_xml, @id_tag_vlr))

			 AND nr_ctrl_if       = dbo.spb_fc_get_node_value(@str_xml, 'NumCtrlLTR')

			 AND guid_operacao    <> @guid_operacao) BEGIN	   



	   EXEC dbo.spb_sp_trata_cont_R0 @str_xml_para, 

	                                 @retorno OUTPUT, 

	                                 @strong_id,

	                                 @str_xml_marcado

 

	   IF @retorno = 1 BEGIN

	      UPDATE spb_tb_pil_operacoes

	         SET flg_desdobrada = 1

	       WHERE guid_operacao = @guid_operacao

	   END

	END

	

	IF @@ERROR = 0 BEGIN

	   COMMIT TRANSACTION

	END

	ELSE BEGIN

	   SET @retorno = 0

	   ROLLBACK TRANSACTION

	END

	

	SET NOCOUNT OFF



END