Skip to Content
avatar image
Former Member

Relatório de Vendas com PIS/COFINS detalhado!!

Amigos,

Montei a seguinte consulta na qual tenho todas as minhas vendas, dentro de um período, com informações básicas (data, valor, cliente, produto, total por linha e etc...)

SELECT T0.[DocDate], T0.[CardCode],T0.[CardName], T0.[Serial],T1.[CFOPCode], T1.[ItemCode], T1.[Dscription], T1.[U_SKILL_UT], T1.[Quantity], T1.[Price], T1.[LineTotal], T0.[Comments]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >='[%0]' AND  T0.[DocDate] <= '[%1]'

Gostaria de incluir as linhas de PIS e COFINS que está sendo cobrada nos itens vendidos... (Não vendo com ICMS e IPI) que estão nesse print:

Alguém pode me ajudar??

Não consegui achar os campos que armazenam essas informações.

Obrigaduuu!!!

Untitled.jpg (370.3 kB)
ee.jpg (55.4 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 25, 2014 at 08:34 PM

    Boa tarde! Tudo bem?

    Tente rodar esta Query

    SELECT T0.[DocDate], T0.[CardCode],T0.[CardName], T0.[Serial],T1.[CFOPCode], T1.[ItemCode], T1.[Dscription],  T1.[U_SKILL_UT],

    T1.[Quantity], T1.[Price], T1.[LineTotal], T0.[Comments], CASE when StaCode like 'CF%' then TaxSum else 0 end as 'CONFINS',

    CASE when StaCode like 'pi%' then TaxSum else 0 end as 'PIS'

    FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

                  INNER JOIN INV4 T2 ON T2.DocEntry = T0.DocEntry

                  WHERE T0.[DocDate] >='[%0]' AND  T0.[DocDate] <= '[%1]' and StaCode like 'CF%' or StaCode like 'PI%'

    Att.

    Mayara

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Raphael,

      Boa tarde, fiz o que você sugeriu: inserir a inner join conforme solicitado e usei a condição do WHERE porem para versão 8.82 ( a versão que uso). No entanto a consulta está retornando que não existem os critérios de seleção (mas existe pelo menos 1 devolução de nota fiscal de saída com seqCode = 29.

      Outra coisa que percebi que na versão 8.82 a coluna AnnInvDecR = NULL , a coluna que é igual ao DocEntry = DocNum ( Já alterei o inner usando essa condição. porem a consulta retorna a o mesmo resultado inicial, ou seja, sem as notas Devolução com seqCode=29)

      SELECT

      T0.DocEntry,

      (ISNULL((SELECT TOP 1 T4.NfmName FROM ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + ISNULL(T0.SeriesStr,'') + ' / ' + CAST(T0.Serial AS VARCHAR) ) as 'Documento',

      T1.LineNum,

      T1.ItemCode,

      T3.ItemName,       

      T0.DocDate,

      T0.CardCode,

      T0.CardName,  

      T1.Quantity,

      CASE WHEN MAX(T0.DiscSum) > 0 then

      ((SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum)) - ((SUM(T1.[TotalSumSy]) / (max(T0.DocTotal)+max(T0.DiscSum))) * max(T0.DiscSum)) )

      / SUM(T1.Quantity)

      ELSE

      (SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum) )

      / SUM(T1.Quantity)

      END AS ValorTotal,

      (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS BaseICMS,

      (SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS AliqICMS,

      (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS ValorICMS,

      (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS BasePTA,

      (SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS AliqPTA,

      (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS ValorPTA,

      (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS BasePPB,

      (SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS AliqPPB,

      (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS ValorPPB,

      (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS BaseIPI,

      (SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS AliqIPI,

      (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS ValorIPI,

      (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS BasePIS,

      (SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS AliqPIS,

      (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS ValorPIS,

      (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS BaseCOF,

      (SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS AliqCOF,

      (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS ValorCOF

      FROM OINV T0

      INNER JOIN ORIN T5 ON (T5.DocEntry = T5.AnnInvDecR)

      INNER JOIN INV1 T1 ON (T1.DocEntry = T0.DocEntry)

      INNER JOIN OITM T3 ON (T3.Itemcode = T1.Itemcode)

      LEFT JOIN ONFM T2 ON T2.AbsEntry = T0.Model

      WHERE

      ISNULL(T2.NfmCode,'') NOT IN ('Outra', 'Nada', 'Fora', 'FAT', '') -- nao entra notas sem modelo definido

      AND T0.DocEntry NOT IN (SELECT T4.BaseEntry FROM ORIN T3 INNER JOIN RIN1 T4 ON T3.DocEntry = T4.DocEntry WHERE T4.BaseEntry IS NOT NULL AND T3.SeqCode = 1) -- Não entra nota cancelada (SAP 8.82)

      AND T5.DocEntry NOT IN (SELECT T4.BaseEntry FROM ORIN T3 INNER JOIN RIN1 T4 ON T3.DocEntry = T4.DocEntry WHERE T4.BaseEntry IS NOT NULL AND T3.SeqCode = 1) -- Não entra nota cancelada (SAP 8.82)

      AND T0.CANCELED = 'N' -- Não entra nota cancelada (SAP 9)

      AND T0.DocDate BETWEEN '20140101' AND '20141212'

      GROUP BY

      T0.DocEntry,

      T1.LineNum,

      T0.SeqCode,

      T0.DocDate,

      T0.CardCode,

      T0.CardName,

      T1.CFOPCode,

      T0.Model,

      T0.SeriesStr,

      T0.Serial,

      T2.NfmCode,

      T1.ItemCode,

      T3.ItemName,

      T1.Quantity

      ORDER BY T0.DocEntry, T1.LineNum

  • Jul 14, 2014 at 05:51 PM

    Boa tarde,

    segue abaixo uma consulta que fiz para ler algumas notas de saída, espero que ajude.

    A tabela de imposto é a INV4

    SELECT 
        T0.DocEntry,
        (ISNULL((SELECT TOP 1 T4.NfmName FROM ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + ISNULL(T0.SeriesStr,'') + ' / ' + CAST(T0.Serial AS VARCHAR) ) as 'Documento',
        T1.LineNum,
        T1.ItemCode,
        T3.ItemName,        
        T0.DocDate,
        T0.CardCode,
        T0.CardName,   
        CASE WHEN MAX(T0.DiscSum) > 0 then
            ((SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum)) - ((SUM(T1.[TotalSumSy]) / (max(T0.DocTotal)+max(T0.DiscSum))) * max(T0.DiscSum)) ) 
            / SUM(T1.Quantity)
        ELSE
            (SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum) ) 
            / SUM(T1.Quantity)
        END AS ValorTotal,
    
        (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS BaseICMS,
        (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS AliqICMS,
        (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS ValorICMS,
        (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS BaseIPI,
        (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS AliqIPI,
        (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS ValorIPI,
        (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS BasePIS,
        (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS AliqPIS,
        (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS ValorPIS,
        (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS BaseCOF,
        (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS AliqCOF,
        (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS ValorCOF
    
    FROM OINV T0
    INNER JOIN INV1 T1 ON (T1.DocEntry = T0.DocEntry)
    INNER JOIN OITM T3 ON (T3.Itemcode = T1.Itemcode)
    LEFT JOIN ONFM T2 ON T2.AbsEntry = T0.Model
    
    WHERE
        ISNULL(T2.NfmCode,'') NOT IN ('Outra', 'Nada', 'Fora', '') -- nao entra notas sem modelo definido
        AND T0.DocEntry NOT IN (SELECT T4.BaseEntry FROM ORIN T3 INNER JOIN RIN1 T4 ON T3.DocEntry = T4.DocEntry WHERE T4.BaseEntry IS NOT NULL AND T3.SeqCode = 1) -- Não entra nota cancelada (SAP 8.82)
        AND T0.CANCELED = 'N' -- Não entra nota cancelada (SAP 9)
        AND T0.DocDate BETWEEN '01/01/2014' AND '31/01/2014'
        
    GROUP BY
        T0.DocEntry,
        T1.LineNum,
        T0.SeqCode,
        T0.DocDate,
        T0.CardCode,
        T0.CardName,
        T1.CFOPCode,
        T0.Model,
        T0.SeriesStr,
        T0.Serial,
        T2.NfmCode,
        T1.ItemCode,
        T3.ItemName
    
    ORDER BY T0.DocEntry, T1.LineNum
    

    Abraço

    Fabio

    SAP 8.82 PL16 / SAP 9.0 PL11 (Test)

    Add comment
    10|10000 characters needed characters exceeded