sql-cab

SELECT
EMP.NOMEFANTASIA,
EMP.RAZAOSOCIAL,
PAR.CODPARC,
PAR.NOMEPARC,
CAB.AD_ROTA,
ITE.NUNOTA,
CAB.NUMNOTA,
ITE.CONTROLE,
ITE.AD_SERIE,
CAB.CODTIPOPER,
CAB.DTNEG,
ITE.CODPROD,
PRO.DESCRPROD,
PRO.NCM,
PRO.AD_CODCEST,
PRO.AD_PARTNUMBER,
ITE.VLRIPI,
ITE.VLRICMS,
ITE.CODCFO,
ITE.BASESUBSTIT,
ITE.ALIQICMS,
ITE.ALIQIPI,
ITE.CODTRIBISS,
ITE.ALIQISS,
ITE.QTDNEG,
ITE.VLRUNIT,
ITE.CODVOL,
ITE.VLRSUBST,
PRO.GRUPOICMS,
ITE.QTDNEG,
ITE.VLRUNIT,
ITE.VLRTOT,
EMP.CODEMP,
ITE.AD_ESTMAQ,
CAB.CODCENCUS,
CUS.DESCRCENCUS,
CAB.APROVADO,
(SELECT MAX(TOP.DESCROPER)
    FROM TGFTOP TOP
    WHERE TOP.CODTIPOPER = CAB.CODTIPOPER) AS DESCRICAO_TOP,
 (SELECT MAX(DIN.ALIQUOTA)
    FROM TGFDIN DIN
    WHERE CAB.STATUSNOTA = 'L'
   AND ITE.NUNOTA = CAB.NUNOTA
    AND ITE.SEQUENCIA > 0
    AND ITE.VLRTOT >= 0
    AND ITE.NUNOTA = DIN.NUNOTA
    AND ITE.SEQUENCIA = DIN.SEQUENCIA
    AND DIN.CODIMP IN (7)
 ) AS COFINS,
 (SELECT MAX(DIN.VALOR)
    FROM TGFDIN DIN
    WHERE CAB.STATUSNOTA = 'L'
   AND ITE.NUNOTA = CAB.NUNOTA
    AND ITE.SEQUENCIA > 0
    AND ITE.VLRTOT >= 0
    AND ITE.NUNOTA = DIN.NUNOTA
    AND ITE.SEQUENCIA = DIN.SEQUENCIA
    AND DIN.CODIMP IN (7)
 ) AS VALOR_COFINS, (SELECT MAX(DIN.BASE)
    FROM TGFDIN DIN
    WHERE CAB.STATUSNOTA = 'L'
   AND ITE.NUNOTA = CAB.NUNOTA
    AND ITE.SEQUENCIA > 0
    AND ITE.VLRTOT >= 0
    AND ITE.NUNOTA = DIN.NUNOTA
    AND ITE.SEQUENCIA = DIN.SEQUENCIA
    AND DIN.CODIMP IN (7)
 ) AS BASECACL_COFINS,
(SELECT MAX(CAB2.VLRNOTA)
FROM TGFCAB CAB2, TGFDIN DIN
    WHERE ITE.NUNOTA = CAB2.NUNOTA
    AND CAB2.STATUSNOTA = 'L'
    AND ITE.SEQUENCIA = 1
    AND CAB2.NUNOTA = DIN.NUNOTA
    AND ITE.SEQUENCIA = DIN.SEQUENCIA) AS VALOR_DA_NOTA
FROM TGFCAB CAB, TGFITE ITE, TGFPRO PRO, TGFPAR PAR
   , TSIEMP EMP, TSICUS CUS, TSICID CID, TSIUFS UFS
WHERE ITE.NUNOTA = CAB.NUNOTA
AND PRO.CODPROD = ITE.CODPROD
AND PAR.CODPARC = CAB.CODPARC
AND EMP.CODEMP = CAB.CODEMP
AND CUS.CODCENCUS = CAB.CODCENCUS
AND PAR.CODCID = CID.CODCID
AND CID.UF = UFS.CODUF
AND CAB.TIPMOV IN ('V')
AND CAB.STATUSNOTA = 'L'
AND ITE.NUNOTA = 458023
-- AND ITE.NUNOTA = 457069
ORDER BY cab.dtneg, ITE.SEQUENCIA;