VENDAS/DEVOLUÇÕES POR VENDEDOR E MÊS

SELECT
CODVEND AS "Cód Vendedor",
APELIDO AS "Vendedor",
TO_CHAR(DTNEG,'YYYY/MM') AS "Ano/Mês",
TIPMOV AS "Tipo Movimento",
ROUND(SUM(VLRNOTATOT),2) AS "Valor"

FROM

(SELECT
    CAB.CODEMP,
    EMP.NOMEFANTASIA,
    CAB.NUNOTA,
    CAB.NUMNOTA,
    CAB.DTNEG,
    CAB.CODPARC,
    PAR.RAZAOSOCIAL,
PAR.CGC_CPF,
    PAR.TIPPESSOA,
    CAB.CODTIPOPER,
    TOP.DESCROPER,
    TOP.GRUPO,
    ITE.CODPROD,
    PRO.DESCRPROD,
    PRO.CODVOL,
    (ITE.QTDNEG*TOP.GOLDEV) AS QTDNEG,
    ITE.VLRUNIT,
    (ITE.VLRTOT*TOP.GOLDEV) AS VLRTOT,
((ITE.QTDNEG * TOP.GOLDEV)* PRO.CONVERVOL) AS CONVERVOL,
    CAB.CODUSUINC,
    USU.NOMEUSU,
    GRU.CODGRUPOPROD,
    GRU.DESCRGRUPOPROD,
    GRU.CODGRUPAI,
    CAB.CODVEND,
    VEN.APELIDO,
    CID.CODCID,
    CID.NOMECID,
    UFS.UF,
    CID.NOMECID    ||' - '||UFS.UF AS CIDADE_UF,
    TO_CHAR(CAB.DTNEG,'YYYY/MM') AS ANO_MES,
    TO_CHAR(CAB.DTNEG,'YYYY') AS ANO,
    TO_CHAR(CAB.DTNEG,'MM') AS MÊS,
    TO_CHAR(CAB.DTNEG,'DD') AS DIA,
    CAB.CODPROJ,
    PRJ.IDENTIFICACAO,
    CAB.CODNAT,
    NAT.DESCRNAT,
    CAB.CODCENCUS,
    CUS.DESCRCENCUS,
    TOP.ATUALFIN,
    CASE WHEN (CAB.CODEMP IN (3,10,12,13,17,18,19) AND CAB.CODPARC IN (10995,18820) AND CAB.NUNOTA NOT IN (559133,559164,559357,559389,559403,559414,559443,559450,559475,559500,559623,560159,560217,560531,560532,560534,560536)) THEN 'FaturamentoSPF' WHEN (CAB.CODEMP IN (1,3,4,5,6,7,8,16) AND CAB.CODPARC IN (13)) THEN 'FaturamentoFHO' ELSE 'Faturamento' END AS RECEITA,
    CAB.TIPMOV,
PRO.MARCA,
    CAB.CIF_FOB,
    CAB.VLRDESCTOTITEM*TOP.ATUALFIN AS DESCONTO,
    CAB.VLRNOTA*TOP.ATUALFIN AS VLRNOTA,
    (SELECT SUM(IT.VLRTOT) FROM TGFITE IT WHERE IT.NUNOTA = CAB.NUNOTA) AS VLRTOTPROD,
    ((CAB.VLRNOTA*TOP.ATUALFIN)/NVL((SELECT SUM(IT.VLRTOT) FROM TGFITE IT WHERE IT.NUNOTA = CAB.NUNOTA),1)*ITE.VLRTOT) AS VLRNOTATOT

FROM TGFCAB CAB
     INNER JOIN TGFITE ITE ON ITE.NUNOTA=CAB.NUNOTA
     INNER JOIN TSIUSU USU ON CAB.CODUSUINC=USU.CODUSU
     INNER JOIN TGFPAR PAR ON PAR.CODPARC=CAB.CODPARC
     INNER JOIN TGFTOP TOP ON CAB.CODTIPOPER=TOP.CODTIPOPER AND CAB.DHTIPOPER=TOP.DHALTER
     INNER JOIN TGFPRO PRO ON PRO.CODPROD=ITE.CODPROD
     INNER JOIN TGFGRU GRU ON GRU.CODGRUPOPROD=PRO.CODGRUPOPROD
     INNER JOIN TGFVEN VEN ON VEN.CODVEND=CAB.CODVEND
     INNER JOIN TSICID CID ON CID.CODCID=PAR.CODCID
     INNER JOIN TSIUFS UFS ON UFS.CODUF = CID.UF
     INNER JOIN TCSPRJ PRJ ON PRJ.CODPROJ = CAB.CODPROJ
     INNER JOIN TGFNAT NAT ON NAT.CODNAT = CAB.CODNAT
     INNER JOIN TSICUS CUS ON CUS.CODCENCUS = CAB.CODCENCUS
     INNER JOIN TSIEMP EMP ON EMP.CODEMP = CAB.CODEMP

WHERE CAB.TIPMOV IN ('V','D','P') AND
      TOP.TIPATUALFIN = 'I' AND
      TOP.ATUALFIN IN (1,-1) AND
      CAB.STATUSNOTA = 'L' AND
      CAB.CODEMP IN (20) AND
      CAB.DTNEG BETWEEN '01/01/2018' AND '31/12/2019'

ORDER BY CAB.DTNEG,CAB.NUNOTA)

GROUP BY
CODVEND,
APELIDO,
TIPMOV,
TO_CHAR(DTNEG,'YYYY/MM')