rsbernini

sql-cab

Nov 27th, 2020
542
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2. EMP.NOMEFANTASIA,
  3. EMP.RAZAOSOCIAL,
  4. PAR.CODPARC,
  5. PAR.NOMEPARC,
  6. CAB.AD_ROTA,
  7. ITE.NUNOTA,
  8. CAB.NUMNOTA,
  9. ITE.CONTROLE,
  10. ITE.AD_SERIE,
  11. CAB.CODTIPOPER,
  12. CAB.DTNEG,
  13. ITE.CODPROD,
  14. PRO.DESCRPROD,
  15. PRO.NCM,
  16. PRO.AD_CODCEST,
  17. PRO.AD_PARTNUMBER,
  18. ITE.VLRIPI,
  19. ITE.VLRICMS,
  20. ITE.CODCFO,
  21. ITE.BASESUBSTIT,
  22. ITE.ALIQICMS,
  23. ITE.ALIQIPI,
  24. ITE.CODTRIBISS,
  25. ITE.ALIQISS,
  26. ITE.QTDNEG,
  27. ITE.VLRUNIT,
  28. ITE.CODVOL,
  29. ITE.VLRSUBST,
  30. PRO.GRUPOICMS,
  31. ITE.QTDNEG,
  32. ITE.VLRUNIT,
  33. ITE.VLRTOT,
  34. EMP.CODEMP,
  35. ITE.AD_ESTMAQ,
  36. CAB.CODCENCUS,
  37. CUS.DESCRCENCUS,
  38. CAB.APROVADO,
  39. (SELECT MAX(TOP.DESCROPER)
  40.     FROM TGFTOP TOP
  41.     WHERE TOP.CODTIPOPER = CAB.CODTIPOPER) AS DESCRICAO_TOP,
  42.  (SELECT MAX(DIN.ALIQUOTA)
  43.     FROM TGFDIN DIN
  44.     WHERE CAB.STATUSNOTA = 'L'
  45.    AND ITE.NUNOTA = CAB.NUNOTA
  46.     AND ITE.SEQUENCIA > 0
  47.     AND ITE.VLRTOT >= 0
  48.     AND ITE.NUNOTA = DIN.NUNOTA
  49.     AND ITE.SEQUENCIA = DIN.SEQUENCIA
  50.     AND DIN.CODIMP IN (7)
  51.  ) AS COFINS,
  52.  (SELECT MAX(DIN.VALOR)
  53.     FROM TGFDIN DIN
  54.     WHERE CAB.STATUSNOTA = 'L'
  55.    AND ITE.NUNOTA = CAB.NUNOTA
  56.     AND ITE.SEQUENCIA > 0
  57.     AND ITE.VLRTOT >= 0
  58.     AND ITE.NUNOTA = DIN.NUNOTA
  59.     AND ITE.SEQUENCIA = DIN.SEQUENCIA
  60.     AND DIN.CODIMP IN (7)
  61.  ) AS VALOR_COFINS, (SELECT MAX(DIN.BASE)
  62.     FROM TGFDIN DIN
  63.     WHERE CAB.STATUSNOTA = 'L'
  64.    AND ITE.NUNOTA = CAB.NUNOTA
  65.     AND ITE.SEQUENCIA > 0
  66.     AND ITE.VLRTOT >= 0
  67.     AND ITE.NUNOTA = DIN.NUNOTA
  68.     AND ITE.SEQUENCIA = DIN.SEQUENCIA
  69.     AND DIN.CODIMP IN (7)
  70.  ) AS BASECACL_COFINS,
  71. (SELECT MAX(CAB2.VLRNOTA)
  72. FROM TGFCAB CAB2, TGFDIN DIN
  73.     WHERE ITE.NUNOTA = CAB2.NUNOTA
  74.     AND CAB2.STATUSNOTA = 'L'
  75.     AND ITE.SEQUENCIA = 1
  76.     AND CAB2.NUNOTA = DIN.NUNOTA
  77.     AND ITE.SEQUENCIA = DIN.SEQUENCIA) AS VALOR_DA_NOTA
  78. FROM TGFCAB CAB, TGFITE ITE, TGFPRO PRO, TGFPAR PAR
  79.    , TSIEMP EMP, TSICUS CUS, TSICID CID, TSIUFS UFS
  80. WHERE ITE.NUNOTA = CAB.NUNOTA
  81. AND PRO.CODPROD = ITE.CODPROD
  82. AND PAR.CODPARC = CAB.CODPARC
  83. AND EMP.CODEMP = CAB.CODEMP
  84. AND CUS.CODCENCUS = CAB.CODCENCUS
  85. AND PAR.CODCID = CID.CODCID
  86. AND CID.UF = UFS.CODUF
  87. AND CAB.TIPMOV IN ('V')
  88. AND CAB.STATUSNOTA = 'L'
  89. AND ITE.NUNOTA = 458023
  90. -- AND ITE.NUNOTA = 457069
  91. ORDER BY cab.dtneg, ITE.SEQUENCIA;
  92.  
  93.  
  94.  
  95.  
  96.  
  97.  
RAW Paste Data