WITH solicitacao AS
(
          SELECT    s.idfilial,
                    s.idsolicitacao,
                    nd.numeronota AS nota_devolucao
          FROM      gdc.solicitacao s
          JOIN      rst.nota n
          ON        n.idfilial = s.idfilial
          AND       n.idregistronota = s.idregistronota
          JOIN      glb.pessoa p
          ON        p.idcnpj_cpf = n.idcnpj_cpf
          LEFT JOIN gdc.solicitacaonotadevolucao snd
          ON        snd.idfilial = s.idfilial
          AND       snd.idregistronota = s.idregistronota
          LEFT JOIN rst.nota nd
          ON        nd.idfilial = snd.idfilial
          AND       nd.idregistronota = snd.idregistronota
          WHERE     s.idfilialcoleta = :idfilialcoleta
          AND       p.cnpj_cpf = :fornecedor )
          , movimentacao AS
(
       SELECT sh.idfilial,
              sh.idsolicitacao,
              st.descricao AS situacao,
              sh.datamovimento,
              MIN(sh.datamovimento) OVER (partition BY sh.idfilial, sh.idsolicitacao) AS datamenor,
              MAX(sh.datamovimento) OVER (partition BY sh.idfilial, sh.idsolicitacao) AS datamaior
       FROM   gdc.solicitacaohistorico sh
       JOIN   solicitacao s
       USING  (idfilial, idsolicitacao)
       JOIN   sis.situacaoautorizacaodevolucao st
       ON     sh.idsituacaoautorizacaodevolucao = st.idsituacaoautorizacaodevolucao ) , termocoleta AS
(
          SELECT    tcs.idfilialsolicitacao,
                    tcs.idsolicitacao,
                    tch.idsituacaotermocoleta,
                    tch.datamovimento,
                    tch.idfilial,
                    tch.idtermocoleta,
                    MAX(tch.datamovimento) OVER (partition BY tch.idfilial, tch.idtermocoleta) AS datamaior
          FROM      solicitacao s
          LEFT JOIN gdc.termocoletasolicitacao tcs
          ON        tcs.idfilialsolicitacao = s.idfilial
          AND       tcs.idsolicitacao = s.idsolicitacao
          LEFT JOIN gdc.termocoletahistorico tch
          ON        tch.idfilial = tcs.idfilial
          AND       tch.idtermocoleta = tcs.idtermocoleta ), solicitacaocomtermo AS
(
       SELECT tc.idfilialsolicitacao,
              tc.idsolicitacao,
              tc.idfilial,
              tc.idtermocoleta
       FROM   termocoleta tc
       WHERE  tc.datamovimento = tc.datamaior
       AND    tc.idsituacaotermocoleta <> 3
              /*Cancelado*/
)
SELECT    ultimohistorico.situacao,
          fs.idempresa,
          fs.idempresa
                    ||'.'
                    ||fs.numerofilial
                    ||' - '
                    || fs.fantasia AS filial,
          fs.numerofilial,
          s.idsolicitacao,
          To_char(primeirohistorico.datamenor, 'dd/mm/yyyy HH24:MI:SS') AS datacadastro,
          n.numeronota,
          ( sct.idfilial = :idfilialcoleta
AND       sct.idtermocoleta = :idtermocoleta ) AS selecionado,
          s.idfilial,
          s.nota_devolucao
FROM      solicitacao s
JOIN      movimentacao UltimoHistorico
ON        ultimohistorico.idfilial = s.idfilial
AND       ultimohistorico.idsolicitacao = s.idsolicitacao
AND       ultimohistorico.datamovimento = ultimohistorico.datamaior
JOIN      movimentacao PrimeiroHistorico
ON        primeirohistorico.idfilial = s.idfilial
AND       primeirohistorico.idsolicitacao = s.idsolicitacao
AND       primeirohistorico.datamovimento = primeirohistorico.datamenor
JOIN      glb.filial fs
ON        fs.idfilial = s.idfilial
JOIN      gdc.solicitacaonotadevolucao snd
ON        snd.idfilial = s.idfilial
AND       snd.idsolicitacao = s.idsolicitacao
JOIN      rst.nota n
ON        n.idfilial = snd.idfilial
AND       n.idregistronota = snd.idregistronota
LEFT JOIN solicitacaocomtermo sct
ON        sct.idfilialsolicitacao = s.idfilial
AND       sct.idsolicitacao = s.idsolicitacao
WHERE     (sct.idsolicitacao IS NULL)