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

[Former Member]

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!!!

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Raphael

Bom dia, estou em busca de uma consulta que me traga basicamente as informações que você usou na consulta acima, porem alem dos impostos normais eu tenho mais 2 (PPB e PTA) na qual preciso que incluir no report.

Você pode me ajudar no complemento dessa informação para que eu possa usar a sua consulta?

Desde já agradeço

[Former Member · Accepted solution]

Fala Ivan...

Poderia explicar o q seria PPB e PTA?

[Former Member · Accepted solution]

Vamos lá:

PTA: é uma "imposto" configurado no B1 que me permite provisionar como valor a recuperar parte do ICMS da venda.

PPB:  é uma "imposto" configurado no B1 que me permite provisionar como valor a recuperar parte do IPI.

Ambos fazem parte da minha combinação de imposto, e se baseiam em percentuais para calculo como os demais impostos.

Então eu preciso de um report que me traga de forma detalhada as notas de saída ( exceto as copiadas para Devolução) com esses dados, que ao meu ver seria um complemento da sua consulta acima, alem do campo de quantidade.

Caso precise visualizar, por favor me mande seu contato.

[Former Member · Accepted solution]

Ivan,

a tabela que armazena os impostos é a INV4, porém para esses impostos que você criou, é necessário saber em qual tabela os criou...

Visto isso, adicione os campos referente na consulta (ex: Tx.[Campo_PPA],Tx.[Campo_PTB],) na sua consulta.

Acredito que isso atenderia sua necessidade....

[Former Member · Accepted solution]

Raphael

Obrigada pela ajuda, deu certo, consulta trazendo os campos devidos.

Só preciso de mais uma ajuda, percebi que sua consulta nao considera notas canceladas, o que esta correto. Porem tenho casos aqui na empresa que preciso cancelar uma nota mesmo após o prazo estipulado de 24hs.

Nesse caso copio a nota de saida p/ devolução de nota de saida, mas preciso validar essa nota na sefaz, ou seja, eu gero uma nota para entrada ( essa nota sua consulta nao esta considerando visto que esta vinculado a um documento de Devolução, mas eu preciso desconsiderar também a nota de saida gerada,

Você pode me ajudar a incluir na consulta essa regra (  nota de saida vinculada a devolução,) será desconsiderada para o Report?

[Former Member · Accepted solution]

Para isto acredito que colocando uma condição WHERE T0.[CANCELED] = 'n' só puxe as notas emitidas e ignore as canceladas...

tente fazer assim e caso funcione, poderia marcar a resposta como "Correct Answer" ou Helpful ???

abs!

[Former Member · Accepted solution]

Raphael

Boa noite, o comando acima eu já tinha feito, talvez não soube me expressar bem na pergunta acima, eu preciso saber como inserir no meu report as NF de devolucao de saída (apenas as que possuem variável SeqCode=29) estou tentando adicionar um select na ORIN mas nao está retornando as NF.

[Former Member · Accepted solution]

Envia teu select por aqui...

[Former Member · Accepted solution]

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 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 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

[Former Member · Accepted solution]

Entendi...

Tenta adicionar um inner join com a ORIN... Adiciona a tabela ORIN na consulta (OINV T0  INNER JOIN ORIN Tx ON Tx.[DocEntry] = Tx.[AnnInvDecR]) e adiciona outra condição de WHERE Tx.[CANCELED] = 'n' ......

Não sei se entendi direito, mas pela minha lógica isso atenderia, pois iria incluir a tabela ORIN na consulta e as notas canceladas dela...

[Former Member · Accepted solution]

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