-- QUERY 1 QUE ESTA DEMORANDO
-- HAVIAM 6 QUERYS IGUAIS A ESSA RODANDO
-- ==========================================================================================
-- Author:      Erik Douglas
-- Create date: 2014-09-11
-- Description: Registros detalhados retornando somente o ultimo status do operador detalhado
-- ==========================================================================================
CREATE PROCEDURE [mbi].[spRelatorio_RegistrosDetalhadosPendentesPorAssunto_INEP]
@DE_ORGAO varchar(128), @NU_NIVEL int, @DE_STATUS varchar(1024), @DT_DATA_INICIO date, @DT_DATA_FIM date, @NU_PROTOCOLO int = 0, @ST_PENDENTES tinyint = 0, @DE_PARA varchar(16) = null, @DE_GRUPO int, @DE_ASSUNTO varchar(2000)
WITH EXEC AS CALLER
AS
BEGIN
        SET NOCOUNT ON
        IF @DE_PARA IS NULL
            BEGIN
                SELECT
                    [TB017].[ID_NOTIFICACAO_SOLICITACAO]  AS [NU_PROTOCOLO]
                    ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS]  AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [TB041_ORGAO_ATUAL].[DE_SIGLA_ORGAO]
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                        [TB041_B].[DE_SIGLA_ORGAO]
                                    ELSE
                                        [TB041_A].[DE_SIGLA_ORGAO]
                                    END
                            END
                    ) AS [DE_ORGAO_SIGLA]
                    ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [dbo].[fnNormalizaOrgaoNivel]([TB041_ORGAO_ATUAL].[NU_NIVEL], DEFAULT)
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_B].[NU_NIVEL], DEFAULT)
                                    ELSE
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_A].[NU_NIVEL], DEFAULT)
                                    END
                            END
                    ) AS [NU_ORGAO_NIVEL]
                    ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [TB041_ORGAO_ATUAL].[DE_ORGAO]
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[DE_ORGAO] IS NOT NULL THEN
                                        [TB041_B].[DE_ORGAO]
                                    ELSE
                                        [TB041_A].[DE_ORGAO]
                                    END
                            END
                    ) AS [DE_ORGAO]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB009].[DE_PESSOA]),'') AS [DE_SOLICITANTE]
                    ,[dbo].[fnNormalizaTexto](
                        [dbo].[fnNormalizaCPF]([TB009].[DE_CPF])
                    , '') AS [DE_SOLICITANTE_CPF]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB009].[DE_CIDADE]), '') AS [DE_SOLICITANTE_CIDADE]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB009].[DE_UF]),'') AS [DE_SOLICITANTE_UF]
                    ,[dbo].[fnNormalizaTexto](
                        [dbo].[fnRetornaPessoaTelefone]([TB009].[ID_PESSOA])
                    ,'') AS [DE_SOLICITANTE_TELEFONE]
                    ,[dbo].[fnNormalizaTexto]([dbo].[fnNormalizaDataHora]([TB017].[DT_CADASTRO]), '') AS [DT_ABERTURA]
--                  ,(
--                      CASE
--                          WHEN [TB056_B].[ID_SECAO] IS NOT NULL THEN
--                              [dbo].[fnRetornaSecaoArvore]([TB056_B].[ID_SECAO])
--                          ELSE
--                              [dbo].[fnRetornaSecaoArvore]([TB056_A].[ID_SECAO])
--                          END
--                  ) AS [DE_DADO_SOLICITACAO]
          --MOSTRA O CAMINHO COMPLETO DA FORMA CORRETA
                    ,(
                        CASE
                            WHEN [DTC001_B].[ID_SECAO] IS NOT NULL THEN
                                [DTC001_B].[DE_SECAO_ARVORE_COMPLETA] + ' -> ' + [TB057_B].[DE_DADO_SOLICITACAO] COLLATE DATABASE_DEFAULT
                            ELSE
                                [DTC001_A].[DE_SECAO_ARVORE_COMPLETA] + ' -> ' + [TB057_A].[DE_DADO_SOLICITACAO] COLLATE DATABASE_DEFAULT
                        END
                    ) AS [DE_DADO_SOLICITACAO]
                    ,dbo.fnDecodificaHTML( dbo.fnNormalizaHTML( dbo.fnDecodificaWord([TB017].[DE_NOTIFICACAO]))) AS [DE_DESCRICAO_SOLICITACAO]
                    ,[dbo].[fnNormalizaTexto]([TB016].[DE_STATUS_NOTIFICACAO], 'RECEBIDO') AS [DE_STATUS_NOTIFICACAO]
                    ,[dbo].[fnNormalizaTexto]([dbo].[fnNormalizaDataHora]([TH001].[DT_ALTERACAO]), '') AS [DT_ALTERACAO]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB005].[DE_USUARIO]), '') AS [DE_USUARIO]
                    ,dbo.fnDecodificaHTML( dbo.fnNormalizaHTML( dbo.fnDecodificaWord([TH001].[DE_RESPOSTA]))) AS [DE_RESPOSTA]
          ,[dbo].[fnNormalizaTexto](
              [dbo].[fnNormalizaDataHora](
                            CASE
                                WHEN [TB050_B].[ID_SLA] IS NOT NULL THEN
                                    (CASE WHEN [TB050_B].[ST_TIPO_SLA] = 0 THEN
                            DATEADD(day,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                          WHEN TB050_B.ST_TIPO_SLA = 1 THEN
                            DATEADD(week,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                          WHEN TB050_B.ST_TIPO_SLA = 2 THEN
                            DATEADD(month,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                          WHEN TB050_B.ST_TIPO_SLA = 3 THEN
                            DATEADD(hour,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                     END)
                                ELSE
                                    (CASE WHEN [TB050_A].[ST_TIPO_SLA] = 0 THEN
                            DATEADD(day,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                          WHEN TB050_A.ST_TIPO_SLA = 1 THEN
                            DATEADD(week,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                          WHEN TB050_A.ST_TIPO_SLA = 2 THEN
                            DATEADD(month,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                          WHEN TB050_A.ST_TIPO_SLA = 3 THEN
                            DATEADD(hour,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                     END)
                            END
              ),
          '') AS TEMPO_SLA_A_VENCER
          ,(
                        CASE
                            WHEN [TB050_B].[ID_SLA] IS NOT NULL THEN
                                (CASE WHEN [TB050_B].[ST_TIPO_SLA] = 0 THEN
                        GETDATE() - DATEADD(day,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                      WHEN TB050_B.ST_TIPO_SLA = 1 THEN
                        GETDATE() - DATEADD(week,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                      WHEN TB050_B.ST_TIPO_SLA = 2 THEN
                        GETDATE() - DATEADD(month,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                      WHEN TB050_B.ST_TIPO_SLA = 3 THEN
                        GETDATE() - DATEADD(hour,TB050_B.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                 END)
                            ELSE
                                (CASE WHEN [TB050_A].[ST_TIPO_SLA] = 0 THEN
                        GETDATE() - DATEADD(day,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                      WHEN TB050_A.ST_TIPO_SLA = 1 THEN
                        GETDATE() - DATEADD(week,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                      WHEN TB050_A.ST_TIPO_SLA = 2 THEN
                        GETDATE() - DATEADD(month,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                      WHEN TB050_A.ST_TIPO_SLA = 3 THEN
                        GETDATE() - DATEADD(hour,TB050_A.NU_TEMPO_SLA,TB017.DT_CADASTRO)
                 END)
                        END
                    ) AS TEMPO_SLA_ATRASO
                FROM
                    [dbo].[TB017_NOTIFICACAO_SOLICITACAO] AS TB017 WITH(NOLOCK)
                    INNER JOIN
                        [dbo].[TB009_PESSOA] AS TB009 WITH(NOLOCK)
                            ON
                                [TB009].[ID_PESSOA] = [TB017].[ID_PESSOA]
                    INNER JOIN
                        [dbo].[TB056_DADO_SOLICITACAO] AS TB056_A WITH(NOLOCK)
                            ON
                                [TB056_A].[ID_DADO_SOLICITACAO] = [TB017].[ID_DADO_SOLICITACAO]
                    INNER JOIN
                        [dbo].[TB041_ORGAO] AS TB041_A WITH(NOLOCK)
                            ON
                                [TB041_A].[ID_ORGAO] = [TB056_A].[ID_ORGAO]
          INNER JOIN
            [dtc].[TB001_SECAO_ARVORE] as DTC001_A WITH(NOLOCK)
              ON [TB056_A].[ID_SECAO] = [DTC001_A].[ID_SECAO]
                    INNER JOIN
                        [dbo].[TB057_DADO_SOLICITACAO_INTERNACIONALIZA] AS TB057_A WITH(NOLOCK)
                            ON
                                [TB057_A].[ID_DADO_SOLICITACAO] = [TB056_A].[ID_DADO_SOLICITACAO]
          INNER JOIN
            [dbo].[TB050_SLA] AS TB050_A WITH(NOLOCK)
              ON
                [TB056_A].[ID_SLA] = [TB050_A].[ID_SLA]
                    LEFT OUTER JOIN
                        [dbo].[TH006_RECATEGORIZACAO_SOLICITACAO] AS TH006 WITH(NOLOCK)
                            ON
                                [TH006].[ID_RECATEGORIZACAO] = [TB017].[ID_HISTORICO_RECATEGORIZACAO_ATUAL]
                    LEFT OUTER JOIN
                        [dbo].[TB056_DADO_SOLICITACAO] AS TB056_B WITH(NOLOCK)
                            ON
                                [TB056_B].[ID_DADO_SOLICITACAO] = [TH006].[ID_DADO_SOLICITACAO_ATUAL]
                    LEFT OUTER  JOIN
                        [dbo].[TB041_ORGAO] AS TB041_B WITH(NOLOCK)
                            ON
                                [TB041_B].[ID_ORGAO] = [TB056_B].[ID_ORGAO]
                    LEFT OUTER JOIN
                        [dbo].[TB057_DADO_SOLICITACAO_INTERNACIONALIZA] AS TB057_B WITH(NOLOCK)
                            ON
                                [TB057_B].[ID_DADO_SOLICITACAO] = [TB056_B].[ID_DADO_SOLICITACAO]
          LEFT OUTER JOIN
            [dtc].[TB001_SECAO_ARVORE] AS DTC001_B
              ON
                [DTC001_B].[ID_SECAO] = [TB056_B].[ID_SECAO]
                    LEFT OUTER JOIN
                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001 WITH(NOLOCK)
                            ON
                                [TH001].[ID_NOTIFICACAO_SOLICITACAO_STATUS] = [TB017].[ID_HISTORICO_ATUAL]
                    LEFT OUTER JOIN
                        [dbo].[TB005_USUARIO] AS TB005 WITH(NOLOCK)
                            ON
                                [TB005].[ID_USUARIO] = [TH001].[ID_USUARIO_ALTERACAO]
                    LEFT OUTER JOIN
                        [dbo].[TB016_STATUS_NOTIFICACAO] AS TB016 WITH(NOLOCK)
                            ON
                                [TB016].[ID_STATUS_NOTIFICACAO] = [TH001].[ID_STATUS_NOTIFICACAO_SOLICITACAO_ATUAL]
          LEFT OUTER JOIN
            [dbo].[TB050_SLA] AS TB050_B WITH(NOLOCK)
              ON
                [TB056_B].[ID_SLA] = [TB050_B].[ID_SLA]
                WHERE [TB017].[ST_STATUS] not in (4) AND
                    (
                        (
                            @NU_PROTOCOLO <> 0
                            AND
                            [TB017].[ID_NOTIFICACAO_SOLICITACAO] = @NU_PROTOCOLO
                        )
                        OR
                        (
                            @NU_PROTOCOLO = 0
                            AND
                            (
                                @DE_STATUS = '--TODOS--'
                                OR
                                [dbo].[fnNormalizaTexto]([TB016].[DE_STATUS_NOTIFICACAO], 'RECEBIDO')  = @DE_STATUS
                            )
                            AND
                            [dbo].[fnValidaOrgao](
                            (
                                CASE
                                    WHEN EXISTS
                                        (
                                            SELECT
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            FROM
                                                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                        )
                                    THEN
                                    (
                                        SELECT
                                            TOP 1
                                            [TB041_ORGAO_ATUAL].[DE_SIGLA_ORGAO]
                                        FROM
                                            [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            INNER JOIN
                                                [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                                ON
                                                    [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                            ORDER BY
                                                [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                                    )
                                    ELSE
                                        CASE
                                            WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                                [TB041_B].[DE_SIGLA_ORGAO]
                                            ELSE
                                                [TB041_A].[DE_SIGLA_ORGAO]
                                            END
                                    END
                            ), @DE_ORGAO) = 1
                            AND
                            [dbo].[fnValidaOrgaoNivel](
                            (
                                CASE
                                    WHEN EXISTS
                                        (
                                            SELECT
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            FROM
                                                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                        )
                                    THEN
                                    (
                                        SELECT
                                            TOP 1
                                            [dbo].[fnNormalizaOrgaoNivel]([TB041_ORGAO_ATUAL].[NU_NIVEL], DEFAULT)
                                        FROM
                                            [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            INNER JOIN
                                                [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                                ON
                                                    [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                            ORDER BY
                                                [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                                    )
                                    ELSE
                                        CASE
                                            WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                                [dbo].[fnNormalizaOrgaoNivel]([TB041_B].[NU_NIVEL], DEFAULT)
                                            ELSE
                                                [dbo].[fnNormalizaOrgaoNivel]([TB041_A].[NU_NIVEL], DEFAULT)
                                            END
                                    END
                            ), @NU_NIVEL, DEFAULT) = 1
                            AND
                            CONVERT(DATE, [TB017].[DT_CADASTRO]) BETWEEN @DT_DATA_INICIO AND @DT_DATA_FIM
                        )
                    )
                    AND
                    (
                        @ST_PENDENTES = 0
                        OR
                        (
                            @ST_PENDENTES = 1
                            AND
                            [dbo].[fnNormalizaTexto]([TB016].[DE_STATUS_NOTIFICACAO], 'RECEBIDO') NOT IN ('FINALIZADO', 'CANCELADO', 'FINALIZADO E ENVIADO AO PORTAL')
                        )
                    )
          AND
          (
            @DE_GRUPO = 0
            OR (@DE_GRUPO <> 0
                AND
                (CASE
                                    WHEN EXISTS
                                        (
                                            SELECT
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            FROM
                                                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                        )
                                    THEN
                                    (
                                        SELECT
                                            TOP 1
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO]
                                        FROM
                                            [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            INNER JOIN
                                                [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                                ON
                                                    [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                            ORDER BY
                                                [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                                    )
                                    ELSE
                                        CASE
                                            WHEN [TB041_B].[ID_ORGAO] IS NOT NULL THEN
                                                [TB041_B].[ID_ORGAO]
                                            ELSE
                                                [TB041_A].[ID_ORGAO]
                                            END
                                    END
                              ) = @DE_GRUPO
              )
          )
          AND
          (
            @DE_ASSUNTO = '--TODOS--'
            OR
            (@DE_ASSUNTO <> '--TODOS--'
             AND
              (CASE
                                    WHEN [DTC001_B].[ID_SECAO] IS NOT NULL THEN
                                      dbo.fnRetiraAcento([DTC001_B].[DE_SECAO_ARVORE_NIVEL_01] + ' -> ' + [DTC001_B].[DE_SECAO_ARVORE_NIVEL_02])
                                    ELSE
                                        dbo.fnRetiraAcento([DTC001_A].[DE_SECAO_ARVORE_NIVEL_01] + ' -> ' + [DTC001_A].[DE_SECAO_ARVORE_NIVEL_02])
                            END) = @DE_ASSUNTO
            )
          )
                ORDER BY
                    [NU_PROTOCOLO] ASC
            END
        IF @DE_PARA = 'FIES'
            BEGIN
                DECLARE @DE_FORM_FIES VARCHAR(32) = 'FRM_FIES_NOVO'
                DECLARE @ID_FORM_FIES INTEGER = (SELECT [ID_FORMULARIO_DINAMICO] FROM [dbo].[TB022_FORMULARIO_DINAMICO] WITH(NOLOCK) WHERE [DE_NOME_TABELA] = @DE_FORM_FIES)

                SELECT
                    [TB017].[ID_NOTIFICACAO_SOLICITACAO] AS [NU_PROTOCOLO]
                    ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [TB041_ORGAO_ATUAL].[DE_SIGLA_ORGAO]
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                        [TB041_B].[DE_SIGLA_ORGAO]
                                    ELSE
                                        [TB041_A].[DE_SIGLA_ORGAO]
                                    END
                            END
                    ) AS [DE_ORGAO_SIGLA]
                    ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [dbo].[fnNormalizaOrgaoNivel]([TB041_ORGAO_ATUAL].[NU_NIVEL], DEFAULT)
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_B].[NU_NIVEL], DEFAULT)
                                    ELSE
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_A].[NU_NIVEL], DEFAULT)
                                    END
                            END
                    ) AS [NU_ORGAO_NIVEL]
                    ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [TB041_ORGAO_ATUAL].[DE_ORGAO]
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[DE_ORGAO] IS NOT NULL THEN
                                        [TB041_B].[DE_ORGAO]
                                    ELSE
                                        [TB041_A].[DE_ORGAO]
                                    END
                            END
                    ) AS [DE_ORGAO]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB009].[DE_PESSOA]),'') AS [DE_SOLICITANTE]
                    ,[dbo].[fnNormalizaTexto](
                        [dbo].[fnNormalizaCPF]([TB009].[DE_CPF])
                    , '') AS [DE_SOLICITANTE_CPF]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB009].[DE_CIDADE]), '') AS [DE_SOLICITANTE_CIDADE]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB009].[DE_UF]),'') AS [DE_SOLICITANTE_UF]
                    ,[dbo].[fnNormalizaTexto](
                        [dbo].[fnRetornaPessoaTelefone]([TB009].[ID_PESSOA])
                    ,'') AS [DE_SOLICITANTE_TELEFONE]
                    ,(
                        CASE
                            WHEN [TB056_B].[ID_DADO_SOLICITACAO] IS NOT NULL THEN
                                CASE
                                    WHEN [TB056_B].[ID_FORMULARIO_DINAMICO] = @ID_FORM_FIES THEN
                                        CASE
                                            WHEN [TB017].[ID_REGISTRO_FORM_DINAMICO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto](UPPER([FRM01].[DE_SIMEC]), '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH (NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                            WHEN [TB017].[ID_FORMULARIO_ULTIMO_REGISTRO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto]([FRM01].[DE_SIMEC], '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH (NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                        ELSE
                                            ''
                                        END
                                ELSE
                                    ''
                                END
                            ELSE
                                CASE
                                    WHEN [TB056_A].[ID_FORMULARIO_DINAMICO] = @ID_FORM_FIES THEN
                                        CASE
                                            WHEN [TB017].[ID_REGISTRO_FORM_DINAMICO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto](UPPER([FRM01].[DE_SIMEC]), '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                            WHEN [TB017].[ID_FORMULARIO_ULTIMO_REGISTRO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto]([FRM01].[DE_SIMEC], '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                        ELSE
                                            ''
                                        END
                                ELSE
                                    ''
                                END
                            END
                    ) AS [NU_SIMEC]
                    ,(
                        CASE
                            WHEN [TB056_B].[ID_DADO_SOLICITACAO] IS NOT NULL THEN
                                CASE
                                    WHEN [TB056_B].[ID_FORMULARIO_DINAMICO] = @ID_FORM_FIES THEN
                                        CASE
                                            WHEN [TB017].[ID_REGISTRO_FORM_DINAMICO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto](UPPER([FRM01].[DE_NOME_EST]), '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                            WHEN [TB017].[ID_FORMULARIO_ULTIMO_REGISTRO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto]([FRM01].[DE_NOME_EST], '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                        ELSE
                                            ''
                                        END
                                ELSE
                                    ''
                                END
                            ELSE
                                CASE
                                    WHEN [TB056_A].[ID_FORMULARIO_DINAMICO] = @ID_FORM_FIES THEN
                                        CASE
                                            WHEN [TB017].[ID_REGISTRO_FORM_DINAMICO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto](UPPER([FRM01].[DE_NOME_EST]), '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                            WHEN [TB017].[ID_FORMULARIO_ULTIMO_REGISTRO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaTexto]([FRM01].[DE_NOME_EST], '' )
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                        ELSE
                                            ''
                                        END
                                ELSE
                                    ''
                                END
                            END
                    ) AS [DE_ESTUDANTE_NOME]
                    ,(
                        CASE
                            WHEN [TB056_B].[ID_DADO_SOLICITACAO] IS NOT NULL THEN
                                CASE
                                    WHEN [TB056_B].[ID_FORMULARIO_DINAMICO] = @ID_FORM_FIES THEN
                                        CASE
                                            WHEN [TB017].[ID_REGISTRO_FORM_DINAMICO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaCPF]([FRM01].[DE_CPF_EST])
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                            WHEN [TB017].[ID_FORMULARIO_ULTIMO_REGISTRO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaCPF]([FRM01].[DE_CPF_EST])
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                        ELSE
                                            ''
                                        END
                                ELSE
                                    ''
                                END
                            ELSE
                                CASE
                                    WHEN [TB056_A].[ID_FORMULARIO_DINAMICO] = @ID_FORM_FIES THEN
                                        CASE
                                            WHEN [TB017].[ID_REGISTRO_FORM_DINAMICO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaCPF]([FRM01].[DE_CPF_EST])
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                            WHEN [TB017].[ID_FORMULARIO_ULTIMO_REGISTRO] IS NOT NULL THEN
                                            (
                                                SELECT
                                                    TOP 1
                                                    [dbo].[fnNormalizaCPF]([FRM01].[DE_CPF_EST])
                                                FROM
                                                    [dbo].[FRM_FIES_NOVO] AS FRM01 WITH(NOLOCK)
                                                WHERE
                                                    [FRM01].[ID] = [TB017].[ID_REGISTRO_FORM_DINAMICO]
                                            )
                                        ELSE
                                            ''
                                        END
                                ELSE
                                    ''
                                END
                            END
                    ) AS [DE_ESTUDANTE_CPF]
                    ,[dbo].[fnNormalizaTexto]([dbo].[fnNormalizaDataHora]([TB017].[DT_CADASTRO]), '') AS [DT_ABERTURA]
                    ,(
                        CASE
                            WHEN [TB056_B].[ID_SECAO] IS NOT NULL THEN
                                [dbo].[fnRetornaSecaoArvore]([TB056_B].[ID_SECAO])
                            ELSE
                                [dbo].[fnRetornaSecaoArvore]([TB056_A].[ID_SECAO])
                            END
                    ) AS [DE_DADO_SOLICITACAO]
                    ,dbo.fnDecodificaHTML( dbo.fnNormalizaHTML( dbo.fnDecodificaWord([TB017].[DE_NOTIFICACAO]))) AS [DE_DESCRICAO_SOLICITACAO]
                    ,[dbo].[fnNormalizaTexto]([TB016].[DE_STATUS_NOTIFICACAO], 'RECEBIDO') AS [DE_STATUS_NOTIFICACAO]
                    ,[dbo].[fnNormalizaTexto]([dbo].[fnNormalizaDataHora]([TH001].[DT_ALTERACAO]), '') AS [DT_ALTERACAO]
                    ,[dbo].[fnNormalizaTexto](UPPER([TB005].[DE_USUARIO]), '') AS [DE_USUARIO]
                    ,dbo.fnDecodificaHTML( dbo.fnNormalizaHTML( dbo.fnDecodificaWord([TH001].[DE_RESPOSTA]))) AS [DE_RESPOSTA]
                FROM
                    [dbo].[TB017_NOTIFICACAO_SOLICITACAO] AS TB017 WITH(NOLOCK)
                    INNER JOIN
                        [dbo].[TB009_PESSOA] AS TB009 WITH(NOLOCK)
                            ON
                                [TB009].[ID_PESSOA] = [TB017].[ID_PESSOA]
                    INNER JOIN
                        [dbo].[TB056_DADO_SOLICITACAO] AS TB056_A WITH(NOLOCK)
                            ON
                                [TB056_A].[ID_DADO_SOLICITACAO] = [TB017].[ID_DADO_SOLICITACAO]
                    INNER JOIN
                        [dbo].[TB041_ORGAO] AS TB041_A WITH(NOLOCK)
                            ON
                                [TB041_A].[ID_ORGAO] = [TB056_A].[ID_ORGAO]
          INNER JOIN
            [dtc].[TB001_SECAO_ARVORE] as DTC001_A WITH(NOLOCK)
              ON [TB056_A].[ID_SECAO] = [DTC001_A].[ID_SECAO]
                    INNER JOIN
                        [dbo].[TB057_DADO_SOLICITACAO_INTERNACIONALIZA] AS TB057_A WITH(NOLOCK)
                            ON
                                [TB057_A].[ID_DADO_SOLICITACAO] = [TB056_A].[ID_DADO_SOLICITACAO]
                    LEFT OUTER JOIN
                        [dbo].[TH006_RECATEGORIZACAO_SOLICITACAO] AS TH006 WITH(NOLOCK)
                            ON
                                [TH006].[ID_RECATEGORIZACAO] = [TB017].[ID_HISTORICO_RECATEGORIZACAO_ATUAL]

                    LEFT OUTER JOIN
                        [dbo].[TB056_DADO_SOLICITACAO] AS TB056_B WITH(NOLOCK)
                            ON
                                [TB056_B].[ID_DADO_SOLICITACAO] = [TH006].[ID_DADO_SOLICITACAO_ATUAL]
                    LEFT OUTER  JOIN
                        [dbo].[TB041_ORGAO] AS TB041_B WITH(NOLOCK)
                            ON
                                [TB041_B].[ID_ORGAO] = [TB056_B].[ID_ORGAO]
          LEFT OUTER JOIN
            [dtc].[TB001_SECAO_ARVORE] as DTC001_B WITH(NOLOCK)
              ON [TB056_B].[ID_SECAO] = [DTC001_B].[ID_SECAO]
                    LEFT OUTER JOIN
                        [dbo].[TB057_DADO_SOLICITACAO_INTERNACIONALIZA] AS TB057_B WITH(NOLOCK)
                            ON
                                [TB057_B].[ID_DADO_SOLICITACAO] = [TB056_B].[ID_DADO_SOLICITACAO]
                    LEFT OUTER JOIN
                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001 WITH(NOLOCK)
                            ON
                                [TH001].[ID_NOTIFICACAO_SOLICITACAO_STATUS] = [TB017].[ID_HISTORICO_ATUAL]
                    LEFT OUTER JOIN
                        [dbo].[TB005_USUARIO] AS TB005 WITH(NOLOCK)
                            ON
                                [TB005].[ID_USUARIO] = [TH001].[ID_USUARIO_ALTERACAO]
                    LEFT OUTER JOIN
                        [dbo].[TB016_STATUS_NOTIFICACAO] AS TB016 WITH(NOLOCK)
                            ON
                                [TB016].[ID_STATUS_NOTIFICACAO] = [TH001].[ID_STATUS_NOTIFICACAO_SOLICITACAO_ATUAL]
                WHERE [TB017].[ST_STATUS] not in (4) AND
                    (
                        (
                            @NU_PROTOCOLO <> 0
                            AND
                            [TB017].[ID_NOTIFICACAO_SOLICITACAO] = @NU_PROTOCOLO
                        )
                        OR
                        (
                            @NU_PROTOCOLO = 0
                            AND
                            (
                                @DE_STATUS = '--TODOS--'
                                OR
                                [dbo].[fnNormalizaTexto]([TB016].[DE_STATUS_NOTIFICACAO], 'RECEBIDO')  = @DE_STATUS
                            )
                            AND
                            [dbo].[fnValidaOrgao](
                            (
                                CASE
                                    WHEN EXISTS
                                        (
                                            SELECT
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            FROM
                                                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                        )
                                    THEN
                                    (
                                        SELECT
                                            TOP 1
                                            [TB041_ORGAO_ATUAL].[DE_SIGLA_ORGAO]
                                        FROM
                                            [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            INNER JOIN
                                                [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                                ON
                                                    [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                            ORDER BY
                                                [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                                    )
                                    ELSE
                                        CASE
                                            WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                                [TB041_B].[DE_SIGLA_ORGAO]
                                            ELSE
                                                [TB041_A].[DE_SIGLA_ORGAO]
                                            END
                                    END
                            ), @DE_ORGAO) = 1
                            AND
                            [dbo].[fnValidaOrgaoNivel](
                            (
                                CASE
                                    WHEN EXISTS
                                        (
                                            SELECT
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            FROM
                                                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                        )
                                    THEN
                                    (
                                        SELECT
                                            TOP 1
                                            [dbo].[fnNormalizaOrgaoNivel]([TB041_ORGAO_ATUAL].[NU_NIVEL], DEFAULT)
                                        FROM
                                            [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            INNER JOIN
                                                [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                                ON
                                                    [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                            ORDER BY
                                                [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                                    )
                                    ELSE
                                        CASE
                                            WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                                [dbo].[fnNormalizaOrgaoNivel]([TB041_B].[NU_NIVEL], DEFAULT)
                                            ELSE
                                                [dbo].[fnNormalizaOrgaoNivel]([TB041_A].[NU_NIVEL], DEFAULT)
                                            END
                                    END
                            ), @NU_NIVEL, DEFAULT) = 1
                            AND
                            CONVERT(DATE, [TB017].[DT_CADASTRO]) BETWEEN @DT_DATA_INICIO AND @DT_DATA_FIM
                        )
                    )
                    AND
                    (
                        @ST_PENDENTES = 0
                        OR
                        (
                            @ST_PENDENTES = 1
                            AND
                            [dbo].[fnNormalizaTexto]([TB016].[DE_STATUS_NOTIFICACAO], 'RECEBIDO') NOT IN ('FINALIZADO', 'CANCELADO', 'FINALIZADO E ENVIADO AO PORTAL')
                        )
                    )
          AND
          (
            @DE_GRUPO = 0
            OR (@DE_GRUPO <> 0
                AND
                (CASE
                                    WHEN EXISTS
                                        (
                                            SELECT
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            FROM
                                                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                        )
                                    THEN
                                    (
                                        SELECT
                                            TOP 1
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO]
                                        FROM
                                            [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            INNER JOIN
                                                [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                                ON
                                                    [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                            ORDER BY
                                                [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                                    )
                                    ELSE
                                        CASE
                                            WHEN [TB041_B].[ID_ORGAO] IS NOT NULL THEN
                                                [TB041_B].[ID_ORGAO]
                                            ELSE
                                                [TB041_A].[ID_ORGAO]
                                            END
                                    END
                              ) = @DE_GRUPO
              )
          )
          AND
          (
            @DE_ASSUNTO = '--TODOS--'
            OR
            (@DE_ASSUNTO <> '--TODOS--'
             AND
              (CASE
                                    WHEN [DTC001_B].[ID_SECAO] IS NOT NULL THEN
                                      dbo.fnRetiraAcento([DTC001_B].[DE_SECAO_ARVORE_NIVEL_01] + ' -> ' + [DTC001_B].[DE_SECAO_ARVORE_NIVEL_02])
                                    ELSE
                                        dbo.fnRetiraAcento([DTC001_A].[DE_SECAO_ARVORE_NIVEL_01] + ' -> ' + [DTC001_A].[DE_SECAO_ARVORE_NIVEL_02])
                            END) = @DE_ASSUNTO
            )
          )
                ORDER BY
                    [NU_PROTOCOLO] ASC
            END
    END


-- QUERY 2 QUE ESTA DEMORANDO

CREATE PROCEDURE [mbi].[spRelatorio_Solicitacoes_sview]
@NU_PROTOCOLO int = 0, @DE_ORGAO varchar(128) = '--TODOS--', @NU_NIVEL int = 0, @DE_ORIGEM varchar(64) = '--TODOS--', @DE_MEIO_COMUNICACAO varchar(128) = '--TODOS--', @DE_STATUS varchar(128) = '--TODOS--', @DE_SECAO varchar(8000) = '--TODOS--', @DE_BAIRRO varchar(128) = '--TODOS--', @DE_CIDADE varchar(128) = '--TODOS--', @DE_ESTADO varchar(2) = '--', @DE_LOGIN_TRATAMENTO varchar(128) = null, @DE_USUARIO_TRATAMENTO varchar(128) = null, @DE_LOGIN_ALTERACAO varchar(128) = null, @DE_USUARIO_ALTERACAO varchar(128) = null, @DT_DATA_CADASTRO date = null, @DT_DATA_CADASTRO_INICIO date = null, @DT_DATA_CADASTRO_FIM date = null, @DT_DATA_ALTERACAO date = null, @DT_DATA_ALTERACAO_INICIO date = null, @DT_DATA_ALTERACAO_FIM date = null
WITH EXEC AS CALLER
AS
BEGIN
        SELECT
            [TB017].[ID_NOTIFICACAO_SOLICITACAO] AS [NU_PROTOCOLO]
            ,ISNULL(UPPER([DC003].[DE_ORIGEM]), NULL) AS [DE_ORIGEM]
            ,[TB008].[ID_MEIO_COMUNICACAO]
            ,[TB008].[DE_MEIO_COMUNICACAO]
            ,(
                CASE
                    WHEN [TB016].[DE_STATUS_NOTIFICACAO] IS NULL AND [TB017].[ID_USUARIO_TRATAMENTO] IS NULL THEN
                        'ABERTA'
                    WHEN [TB016].[DE_STATUS_NOTIFICACAO] IS NULL AND [TB017].[ID_USUARIO_TRATAMENTO] IS NOT NULL THEN
                        'EM ANDAMENTO'
                    ELSE
                        [TB016].[DE_STATUS_NOTIFICACAO]
                    END
            ) AS [DE_STATUS]
    ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [TB041_ORGAO_ATUAL].[ID_ORGAO]
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[ID_ORGAO] IS NOT NULL THEN
                                        [TB041_B].[ID_ORGAO]
                                    ELSE
                                        [TB041_A].[ID_ORGAO]
                                    END
                            END
                    ) AS [ID_ORGAO]
                                ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [TB041_ORGAO_ATUAL].[DE_ORGAO]
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[DE_ORGAO] IS NOT NULL THEN
                                        [TB041_B].[DE_ORGAO]
                                    ELSE
                                        [TB041_A].[DE_ORGAO]
                                    END
                            END
                    ) AS [DE_ORGAO]
            ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [dbo].[fnNormalizaOrgaoNivel]([TB041_ORGAO_ATUAL].[NU_NIVEL], DEFAULT)
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_B].[NU_NIVEL], DEFAULT)
                                    ELSE
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_A].[NU_NIVEL], DEFAULT)
                                    END
                            END
                    ) AS [NU_ORGAO_NIVEL]

      ,(
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [TB041_ORGAO_ATUAL].[DE_SIGLA_ORGAO]
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                        [TB041_B].[DE_SIGLA_ORGAO]
                                    ELSE
                                        [TB041_A].[DE_SIGLA_ORGAO]
                                    END
                            END
                    ) AS [DE_ORGAO_SIGLA]

        --  ,UPPER([TB041].[DE_SIGLA_ORGAO]) AS [DE_ORGAO_SIGLA]
            ,[TB009].[ID_PESSOA] AS [ID_SOLICITANTE]
            ,(
                CASE
                    WHEN [TB009].[ST_TIPO_PESSOA] = 1 THEN
                        'PESSOA JURÍDICA'
                    ELSE
                        'PESSOA FÍSICA'
                    END
            ) AS [DE_SOLICITANTE_TIPO]
            ,UPPER([TB009].[DE_PESSOA]) AS [DE_SOLICITANTE_NOME]
            ,(
                CASE
                    WHEN [TB009].[ST_TIPO_PESSOA] = 1 AND [TB009].[DE_NOME_FANTASIA] IS NOT NULL THEN
                        UPPER([TB009].[DE_NOME_FANTASIA])
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_NOME_FANTASIA]
            ,(
                CASE
                    WHEN [TB009].[ST_TIPO_PESSOA] = 1 THEN
                        NULL
                    ELSE
                        CASE
                            WHEN [TB009].[ST_SEXO] = 0 THEN
                                'FEMININO'
                            WHEN [TB009].[ST_SEXO] = 1 THEN
                                'MASCULINO'
                            ELSE
                                NULL
                            END
                    END
            ) AS [DE_SOLICITANTE_SEXO]
            ,(
                CASE
                    WHEN [TB009].[ST_TIPO_PESSOA] = 1 THEN
                        NULL
                    ELSE
                        [dbo].[fnValida_CPF]([TB009].[DE_CPF], 1)
                    END
            ) AS [DE_SOLICITANTE_CPF]
            ,(
                CASE
                    WHEN [TB009].[ST_TIPO_PESSOA] = 1 THEN
                        NULL
                    ELSE
                        [TB009].[DE_RG]
                    END
            ) AS [DE_SOLICITANTE_RG]
            ,(
                CASE
                    WHEN [TB009].[ST_TIPO_PESSOA] = 1 THEN
                        [dbo].[fnValida_CNPJ]([TB009].[DE_CNPJ], 1)
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_CNPJ]
            ,(
                CASE
                    WHEN [TB009].[ST_TIPO_PESSOA] = 1 THEN
                        UPPER([TB009].[DE_INSCRICAO_ESTADUAL])
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_IE]
            ,(
                CASE
                    WHEN [TB009].[DE_EMAIL] IS NOT NULL AND LEN(LTRIM(RTRIM([DE_EMAIL]))) > 0 THEN
                        LOWER([TB009].[DE_EMAIL])
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_EMAIL]
            ,[DC004].[DE_TELEFONE] AS [DE_SOLICITANTE_TELEFONE]
            ,(
                CASE
                    WHEN [TB009].[DE_CEP] IS NOT NULL THEN
                        [dbo].[fnValida_CEP]([TB009].[DE_CEP], 1)
                    ELSE
                        NULL
                    END
            ) [DE_SOLICITANTE_CEP]
            ,(
                CASE
                    WHEN [TB009].[DE_LOGRADOURO] IS NOT NULL AND LEN(LTRIM(RTRIM([TB009].[DE_LOGRADOURO]))) > 0 THEN
                        CASE
                            WHEN  [TB009].[NU_LOGRADOURO] IS NOT NULL AND LEN(LTRIM(RTRIM([TB009].[NU_LOGRADOURO]))) > 0 THEN
                                UPPER([TB009].[DE_LOGRADOURO]) +  ', ' +  [TB009].[NU_LOGRADOURO]
                            ELSE
                                [TB009].[DE_LOGRADOURO]
                            END
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_ENDERECO]
            ,(
                CASE
                    WHEN [TB009].[DE_COMPLEMENTO] IS NOT NULL AND LEN(LTRIM(RTRIM([TB009].[DE_COMPLEMENTO]))) > 0 THEN
                        UPPER([TB009].[DE_COMPLEMENTO])
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_COMPLEMENTO]
            ,(
                CASE
                    WHEN [TB009].[DE_BAIRRO] IS NOT NULL AND LEN(LTRIM(RTRIM([TB009].[DE_BAIRRO]))) > 0 THEN
                        UPPER([TB009].[DE_BAIRRO])
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_BAIRRO]
            ,(
                CASE
                    WHEN [TB009].[DE_CIDADE] IS NOT NULL AND LEN(LTRIM(RTRIM([TB009].[DE_CIDADE]))) > 0 THEN
                        UPPER([TB009].[DE_CIDADE])
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_CIDADE]
            ,(
                CASE
                    WHEN [TB009].[DE_UF] IS NOT NULL AND LEN(LTRIM(RTRIM([TB009].[DE_UF]))) > 0 THEN
                        UPPER([TB009].[DE_UF])
                    ELSE
                        NULL
                    END
            ) AS [DE_SOLICITANTE_ESTADO]
            ,ISNULL(UPPER([TB005_TRATAMENTO].[DE_LOGIN]), NULL) AS [DE_TRATAMENTO_LOGIN]
            ,(
                CASE
                    WHEN [TB005_TRATAMENTO].[DE_USUARIO] IS NOT NULL THEN
                        UPPER([TB005_TRATAMENTO].[DE_USUARIO])
                    ELSE
                        NULL
                    END
            ) AS [DE_TRATAMENTO_USUARIO]
            ,[TB017].[DT_CADASTRO]
            ,[TH001].[DT_ALTERACAO]
            ,ISNULL(UPPER([TB005_ALTERACAO].[DE_LOGIN]), NULL) AS [DE_ALTERACAO_LOGIN]
            ,(
                CASE
                    WHEN [TB005_ALTERACAO].[DE_USUARIO] IS NOT NULL THEN
                        UPPER([TB005_ALTERACAO].[DE_USUARIO])
                    ELSE
                        NULL
                    END
            ) AS [DE_ALTERACAO_USUARIO]
            ,(
                CASE
                    WHEN [DC001_RECATEGORIZADO].[ID_SECAO] IS NOT NULL THEN
                        [DC001_RECATEGORIZADO].[ID_SECAO]
                    ELSE
                        [DC001_BASE].[ID_SECAO]
              END
            ) AS [ID_SECAO]
            ,(
                CASE
                    WHEN [DC001_RECATEGORIZADO].[DE_SECAO_ARVORE_COMPLETA] IS NOT NULL THEN
                        UPPER([DC001_RECATEGORIZADO].[DE_SECAO_ARVORE_COMPLETA])
                    ELSE
                        UPPER([DC001_BASE].[DE_SECAO_ARVORE_COMPLETA])
                END
            ) AS [DE_SECAO]
            ,(
                CASE
                    WHEN [TB057_RECATEGORIZADO].[ID_DADO_SOLICITACAO] IS NOT NULL THEN
                        [TB057_RECATEGORIZADO].[ID_DADO_SOLICITACAO]
                    ELSE
                        [TB057_BASE].[ID_DADO_SOLICITACAO]
                END
            ) AS [ID_CONTEUDO]
            ,(
                CASE
                    WHEN [TB057_RECATEGORIZADO].[DE_DADO_SOLICITACAO] IS NOT NULL THEN
                        UPPER([TB057_RECATEGORIZADO].[DE_DADO_SOLICITACAO])
                    ELSE
                        UPPER([TB057_BASE].[DE_DADO_SOLICITACAO])
                END
            ) AS [DE_CONTEUDO]
            ,[dbo].[fnNormalizaTexto]([TB017].[DE_NOTIFICACAO],'') AS [DE_SOLICITACAO]
            ,(
                CASE
                    WHEN [TH001].[DE_RESPOSTA] IS NOT NULL THEN
                        dbo.fnDecodificaHTML( dbo.fnNormalizaHTML( dbo.fnDecodificaWord([TH001].[DE_RESPOSTA])))
                    ELSE
                        NULL
                    END
            ) AS [DE_RESPOSTA]
        FROM
            [dbo].[TB017_NOTIFICACAO_SOLICITACAO] AS TB017 WITH (NOLOCK)
            INNER JOIN
                [dbo].[TB008_MEIO_COMUNICACAO] AS TB008 WITH (NOLOCK)
                    ON
                        [TB008].[ID_MEIO_COMUNICACAO] = [TB017].[ID_MEIO_COMUNICACAO]
            INNER JOIN
                [dbo].[TB009_PESSOA] AS TB009 WITH (NOLOCK)
                    ON
                        [TB009].[ID_PESSOA] = [TB017].[ID_PESSOA]
            LEFT JOIN
                [dtc].[TB004_PESSOA_TELEFONE] AS DC004 WITH (NOLOCK)
                    ON
                        [DC004].[ID_PESSOA] = [TB009].[ID_PESSOA]
        LEFT JOIN
                [dtc].[TB003_NOTIFICACAO_SOLICITACAO_ORIGEM] AS DC003 WITH (NOLOCK)
                    ON
                        [DC003].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
--          INNER JOIN
--              [dtc].[TB002_NOTIFICACAO_SOLICITACAO_ORGAO] AS DC002 WITH (NOLOCK)
--                  ON
--                      [DC002].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]

            INNER JOIN
                [dbo].[TB056_DADO_SOLICITACAO] AS TB056_BASE WITH(NOLOCK)
                    ON
                        [TB056_BASE].[ID_DADO_SOLICITACAO] = [TB017].[ID_DADO_SOLICITACAO]

      INNER JOIN
                        [dbo].[TB041_ORGAO] AS TB041_A WITH(NOLOCK)
                            ON
                                [TB041_A].[ID_ORGAO] = [TB056_BASE].[ID_ORGAO]

            INNER JOIN
                [dbo].[TB057_DADO_SOLICITACAO_INTERNACIONALIZA] AS TB057_BASE WITH(NOLOCK)
                    ON
                        [TB057_BASE].[ID_DADO_SOLICITACAO] = [TB056_BASE].[ID_DADO_SOLICITACAO]
                        AND
                        [TB057_BASE].[DE_LOCALE] = 'PORTUGUES'
            INNER JOIN
                [dtc].[TB001_SECAO_ARVORE] AS DC001_BASE WITH (NOLOCK)
                    ON
                        [DC001_BASE].[ID_SECAO] = [TB056_BASE].[ID_SECAO]
            LEFT JOIN
                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001 WITH (NOLOCK)
                    ON [TH001].[ID_NOTIFICACAO_SOLICITACAO_STATUS] = [TB017].[ID_HISTORICO_ATUAL]

            LEFT JOIN
                [dbo].[TB016_STATUS_NOTIFICACAO] AS TB016 WITH (NOLOCK)
                    ON
                        [TB016].[ID_STATUS_NOTIFICACAO] = [TH001].[ID_STATUS_NOTIFICACAO_SOLICITACAO_ATUAL]
            LEFT OUTER JOIN
                [dbo].[TH006_RECATEGORIZACAO_SOLICITACAO] AS TH006 WITH(NOLOCK)
                    ON
                        [TH006].[ID_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
            AND TH006.ID_RECATEGORIZACAO = (SELECT MAX(TH006_DENTRO.ID_RECATEGORIZACAO)
                                            FROM TH006_RECATEGORIZACAO_SOLICITACAO AS TH006_DENTRO
                                            WHERE TH006_DENTRO.ID_SOLICITACAO = TB017.ID_NOTIFICACAO_SOLICITACAO
                                            )
            LEFT OUTER JOIN
                [dbo].[TB056_DADO_SOLICITACAO] AS TB056_RECATEGORIZADO WITH(NOLOCK)
                    ON
                        [TB056_RECATEGORIZADO].[ID_DADO_SOLICITACAO] = [TH006].[ID_DADO_SOLICITACAO_ATUAL]
      LEFT OUTER  JOIN
                        [dbo].[TB041_ORGAO] AS TB041_B WITH(NOLOCK)
                            ON
                                [TB041_B].[ID_ORGAO] = [TB056_RECATEGORIZADO].[ID_ORGAO]
            LEFT OUTER JOIN
                [dbo].[TB057_DADO_SOLICITACAO_INTERNACIONALIZA] AS TB057_RECATEGORIZADO WITH(NOLOCK)
                    ON
                        [TB057_RECATEGORIZADO].[ID_DADO_SOLICITACAO] = [TB056_RECATEGORIZADO].[ID_DADO_SOLICITACAO]
                        AND
                        [TB057_RECATEGORIZADO].[DE_LOCALE] = 'PORTUGUES'
            LEFT OUTER JOIN
                [dtc].[TB001_SECAO_ARVORE] AS DC001_RECATEGORIZADO WITH (NOLOCK)
                    ON
                        [DC001_RECATEGORIZADO].[ID_SECAO] = [TB056_RECATEGORIZADO].[ID_SECAO]
            LEFT OUTER JOIN
                [dbo].[TB005_USUARIO] AS TB005_TRATAMENTO WITH (NOLOCK)
                    ON
                        [TB005_TRATAMENTO].[ID_USUARIO] = [TB017].[ID_USUARIO_TRATAMENTO]
            LEFT OUTER JOIN
                [dbo].[TB005_USUARIO] AS TB005_ALTERACAO WITH (NOLOCK)
                    ON
                        [TB005_ALTERACAO].[ID_USUARIO] = [TH001].[ID_USUARIO_ALTERACAO]
        WHERE
            [TB017].[ST_STATUS] IN (0,1,2)
            AND
            (
                @NU_PROTOCOLO = 0
                OR
                (
                    @NU_PROTOCOLO <> 0
                    AND
                    [TB017].[ID_NOTIFICACAO_SOLICITACAO] = @NU_PROTOCOLO
                )
            )
        AND
                            [dbo].[fnValidaOrgao](
                            (
                                CASE
                                    WHEN EXISTS
                                        (
                                            SELECT
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            FROM
                                                [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                        )
                                    THEN
                                    (
                                        SELECT
                                            TOP 1
                                            [TB041_ORGAO_ATUAL].[DE_SIGLA_ORGAO]
                                        FROM
                                            [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                            INNER JOIN
                                                [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                                ON
                                                    [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                            WHERE
                                                [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                                AND
                                                [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                            ORDER BY
                                                [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                                    )
                                    ELSE
                                        CASE
                                            WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                                [TB041_B].[DE_SIGLA_ORGAO]
                                            ELSE
                                                [TB041_A].[DE_SIGLA_ORGAO]
                                            END
                                    END
                            ), @DE_ORGAO) = 1
            AND
                    [dbo].[fnValidaORgaoNivel]((
                        CASE
                            WHEN EXISTS
                                (
                                    SELECT
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    FROM
                                        [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                )
                            THEN
                            (
                                SELECT
                                    TOP 1
                                    [dbo].[fnNormalizaOrgaoNivel]([TB041_ORGAO_ATUAL].[NU_NIVEL], DEFAULT)
                                FROM
                                    [dbo].[TH001_NOTIFICACAO_SOLICITACAO_STATUS] AS TH001_ORGAO_ATUAL WITH(NOLOCK)
                                    INNER JOIN
                                        [dbo].[TB041_ORGAO] AS TB041_ORGAO_ATUAL WITH(NOLOCK)
                                        ON
                                            [TB041_ORGAO_ATUAL].[ID_ORGAO] = [TH001_ORGAO_ATUAL].[ID_ORGAO]
                                    WHERE
                                        [TH001_ORGAO_ATUAL].[ID_ORGAO] IS NOT NULL
                                        AND
                                        [TH001_ORGAO_ATUAL].[ID_NOTIFICACAO_SOLICITACAO] = [TB017].[ID_NOTIFICACAO_SOLICITACAO]
                                    ORDER BY
                                        [TH001_ORGAO_ATUAL].[DT_ALTERACAO] DESC
                            )
                            ELSE
                                CASE
                                    WHEN [TB041_B].[NU_NIVEL] IS NOT NULL THEN
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_B].[NU_NIVEL], DEFAULT)
                                    ELSE
                                        [dbo].[fnNormalizaOrgaoNivel]([TB041_A].[NU_NIVEL], DEFAULT)
                                    END
                            END
                    ), @NU_NIVEL, DEFAULT) = 1

            AND
            (
                @DE_ORIGEM = '--TODOS--'
                OR
                (
                    @DE_ORIGEM <> '--TODOS--'
                    AND
                    [DC003].[DE_ORIGEM] = @DE_ORIGEM
                )
            )
            AND
            (
                @DE_MEIO_COMUNICACAO = '--TODOS--'
                OR
                (
                    @DE_MEIO_COMUNICACAO <> '--TODOS--'
                    AND
                    [TB008].[DE_MEIO_COMUNICACAO] = @DE_MEIO_COMUNICACAO
                )
            )
            AND
            (
                @DE_STATUS = '--TODOS--'
                OR
                (
                    @DE_STATUS <> '--TODOS--'
                    AND
                    (
                        CASE
                            WHEN [TB016].[DE_STATUS_NOTIFICACAO] IS NULL AND [TB017].[ID_USUARIO_TRATAMENTO] IS NULL THEN
                                'ABERTA'
                            WHEN [TB016].[DE_STATUS_NOTIFICACAO] IS NULL AND [TB017].[ID_USUARIO_TRATAMENTO] IS NOT NULL THEN
                                'EM ANDAMENTO'
                            ELSE
                                [TB016].[DE_STATUS_NOTIFICACAO]
                            END
                    ) = @DE_STATUS
                )
            )
            AND
            (
                @DE_SECAO = '--TODOS--'
                OR
                (
                    @DE_SECAO <> '--TODOS--'
                    AND
                    (
                        CASE
                            WHEN [DC001_RECATEGORIZADO].[DE_SECAO_ARVORE_COMPLETA] IS NOT NULL THEN
                                UPPER([DC001_RECATEGORIZADO].[DE_SECAO_ARVORE_COMPLETA])
                            ELSE
                                UPPER([DC001_BASE].[DE_SECAO_ARVORE_COMPLETA])
                            END
                    ) = @DE_SECAO
                )
            )
            AND
            (
                @DE_BAIRRO = '--TODOS--'
                OR
                (
                    @DE_BAIRRO <> '--TODOS--'
                    AND
                    [TB009].[DE_BAIRRO] = @DE_BAIRRO
                )
            )
            AND
            (
                @DE_CIDADE = '--TODOS--'
                OR
                (
                    @DE_CIDADE <> '--TODOS--'
                    AND
                    [TB009].[DE_CIDADE] = @DE_CIDADE
                )
            )
            AND
            (
                @DE_ESTADO = '--'
                OR
                (
                    @DE_ESTADO <> '--'
                    AND
                    [TB009].[DE_UF] = @DE_ESTADO
                )
            )
            AND
            (
                (
                    @DE_LOGIN_TRATAMENTO IS NULL
                    AND
                    @DE_LOGIN_ALTERACAO IS NULL
                )
                OR
                (
                    @DE_LOGIN_TRATAMENTO IS NOT NULL
                    AND
                    @DE_LOGIN_ALTERACAO IS NOT NULL
                    AND
                    (
                        [TB005_TRATAMENTO].[DE_LOGIN] = @DE_LOGIN_TRATAMENTO
                        OR
                        [TB005_ALTERACAO].[DE_LOGIN] = @DE_LOGIN_ALTERACAO
                    )
                )
                OR
                (
                    @DE_LOGIN_TRATAMENTO IS NOT NULL
                    AND
                    @DE_LOGIN_ALTERACAO IS NULL
                    AND
                    [TB005_TRATAMENTO].[DE_LOGIN] = @DE_LOGIN_TRATAMENTO
                )
                OR
                (
                    @DE_LOGIN_TRATAMENTO IS NULL
                    AND
                    @DE_LOGIN_ALTERACAO IS NOT NULL
                    AND
                    [TB005_ALTERACAO].[DE_LOGIN] = @DE_LOGIN_ALTERACAO
                )
            )
            AND
            (
                (
                    @DE_USUARIO_TRATAMENTO IS NULL
                    AND
                    @DE_USUARIO_ALTERACAO IS NULL
                )
                OR
                (
                    @DE_USUARIO_TRATAMENTO IS NOT NULL
                    AND
                    @DE_USUARIO_ALTERACAO IS NOT NULL
                    AND
                    (
                        [TB005_TRATAMENTO].[DE_LOGIN] = @DE_USUARIO_TRATAMENTO
                        OR
                        [TB005_ALTERACAO].[DE_LOGIN] = @DE_USUARIO_ALTERACAO
                    )
                )
                OR
                (
                    @DE_USUARIO_TRATAMENTO IS NOT NULL
                    AND
                    @DE_USUARIO_ALTERACAO IS NULL
                    AND
                    [TB005_TRATAMENTO].[DE_LOGIN] = @DE_USUARIO_TRATAMENTO
                )
                OR
                (
                    @DE_USUARIO_TRATAMENTO IS NULL
                    AND
                    @DE_USUARIO_ALTERACAO IS NOT NULL
                    AND
                    [TB005_ALTERACAO].[DE_LOGIN] = @DE_USUARIO_ALTERACAO
                )
            )
            AND
            (
                (
                    @DT_DATA_CADASTRO IS NULL
                    AND
                    @DT_DATA_CADASTRO_INICIO IS NULL
                    AND
                    @DT_DATA_CADASTRO_FIM IS NULL
                )
                OR
                (
                    @DT_DATA_CADASTRO IS NOT NULL
                    AND
                    CONVERT(DATE, [TB017].[DT_CADASTRO]) = @DT_DATA_CADASTRO
                )
                OR
                (
                    @DT_DATA_CADASTRO IS NULL
                    AND
                    @DT_DATA_CADASTRO_INICIO IS NOT NULL
                    AND
                    @DT_DATA_CADASTRO_FIM IS NOT NULL
                    AND
                    CONVERT(DATE, [TB017].[DT_CADASTRO]) BETWEEN @DT_DATA_CADASTRO_INICIO AND @DT_DATA_CADASTRO_FIM
                )
            )
            AND
            (
                (
                    @DT_DATA_ALTERACAO IS NULL
                    AND
                    @DT_DATA_ALTERACAO_INICIO IS NULL
                    AND
                    @DT_DATA_ALTERACAO_FIM IS NULL
                )
                OR
                (
                    @DT_DATA_ALTERACAO IS NOT NULL
                    AND
                    CONVERT(DATE, [TB017].[DT_CADASTRO]) = @DT_DATA_ALTERACAO
                )
                OR
                (
                    @DT_DATA_ALTERACAO IS NULL
                    AND
                    @DT_DATA_ALTERACAO_INICIO IS NOT NULL
                    AND
                    @DT_DATA_ALTERACAO_FIM IS NOT NULL
                    AND
                    [TH001].[DT_ALTERACAO] IS NOT NULL
                    AND
                    CONVERT(DATE, [TH001].[DT_ALTERACAO]) BETWEEN @DT_DATA_ALTERACAO_INICIO AND @DT_DATA_ALTERACAO_FIM
                )
            )
    END